Showing posts with label line. Show all posts
Showing posts with label line. Show all posts

Monday, March 12, 2012

programmatically setting package variables in job step command line

We are trying to start a server job running an SSIS package and supply some parameters to the package when we start the job using SMO.

What we have now is this:

string cmdLine = job.JobSteps[0].Command;

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::GroupId].Value;1";

cmdLine += " /SET \\Package.Variables[User::period].Value;\"" + periodEndDate + "\"";

job.JobSteps[0].Command = cmdLine;

job.Start();

It appears that when the job is run, the modified command line is not used.

What is needed to supply runtime parameters to a job step when starting the job via SMO?

Thanks,

So managing a job in this way seems a bit of a pain. Why not let the package go and get the values from an external location when it is required.

One example would be to use a package configuration, perhaps using a SQL Server configuration. You could update the table values, and then just design the package to use that configuration value, assigning the values to the variables as required. Read up on package configurations if you are not familiar with them.

A variation on the theme it to do the work yourself. You could use any table, not just a configuration format table. Use an Execuite SQL Task to query for the values and using the result set option, you can return values and on the results page of the task, set the output to variable values.

|||

Yes it's been a learning curve in how to do what we're trying to do. The application is driven by a web page where the user says 'run this job, use these parameters'. However, you can't have one predefined job with multiple instances on the server, each with their own set of parameters - which needs to be possible because of the application requirements.

What we are doing now that seems to work is creating a new job, setting the type to ssis, setting the command line to specify the package and parameters, and then starting the job. It is also set to auto delete upon success.

The other way we thought of but decided against was to have the job pick up its runtime parameters from a queue - but then we'd have to create and manage the queue.

The 'create new job' approach lets us run now or set a schedule to run later, all the instances are visible as jobs on the server (based on category to filter out for the UI), and they clean up themselves if they run successfully.

NB: if anyone is curious, changing the command line of an existing job requires the Alter() method to persist the change back to the server, otherwise it just runs with the original command. like this:

string cmdLine = job.JobSteps[0].Command;
cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";
job.JobSteps[0].Command = cmdLine;
job.JobSteps[0].Alter();
job.Start();

However, this permanently changes the command line in the job of course and you have to deal with that.

The code that that we're using to dynamically create the job and supply the parameters is pretty close to this:

string jobName = "the name to give to the new job";
string cmdLine = "the command line to run the package and set parameters";
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server svr = new Server(svrConnection);
JobServer agent = svr.JobServer;
if (agent.Jobs.Contains(jobName))
{
agent.Jobs[jobName].Drop();
}

job = new Job(agent, jobName);
job.DeleteLevel = CompletionAction.OnSuccess;
job.Category = "Calculate";
JobStep js = new JobStep(job, "Step 1");
js.SubSystem = AgentSubSystem.Ssis;
js.Command = cmdLine;
job.Create();
js.Create();
job.ApplyToTargetServer("(local)");
job.Alter();
job.Start();

Monday, February 20, 2012

program return blank set

Hi NGs,
I have a program connecting to SQL Server as the back-
end, recently, our company change to use FDDI instead of
128K network line; this causes the program return blank
Set from the SQL Server. We don't modify the program, the
only change is the networking situation. From 128k line
to FDDI; the program worked fine for about 1 month before
we made the change; FDDI also works great except this
program. Do you have any idea?
TIA.Are you able to make a connection to the SQL Server outside of the
application using tools SQL Query Analyser or OSQL.
HTH
Ryan Waight, MCDBA, MCSE
"Matt" <spam@.spam.com> wrote in message
news:079801c3ae80$22a1ecb0$a301280a@.phx.gbl...
> Hi NGs,
> I have a program connecting to SQL Server as the back-
> end, recently, our company change to use FDDI instead of
> 128K network line; this causes the program return blank
> Set from the SQL Server. We don't modify the program, the
> only change is the networking situation. From 128k line
> to FDDI; the program worked fine for about 1 month before
> we made the change; FDDI also works great except this
> program. Do you have any idea?
> TIA.
>
>
>|||thanks, currently I don't know, I will try it, if it
connects successfully, how do I process in next step?
>--Original Message--
>Are you able to make a connection to the SQL Server
outside of the
>application using tools SQL Query Analyser or OSQL.
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Matt" <spam@.spam.com> wrote in message
>news:079801c3ae80$22a1ecb0$a301280a@.phx.gbl...
>> Hi NGs,
>> I have a program connecting to SQL Server as the back-
>> end, recently, our company change to use FDDI instead
of
>> 128K network line; this causes the program return blank
>> Set from the SQL Server. We don't modify the program,
the
>> only change is the networking situation. From 128k line
>> to FDDI; the program worked fine for about 1 month
before
>> we made the change; FDDI also works great except this
>> program. Do you have any idea?
>> TIA.
>>
>>
>>
>
>.
>|||Hi Matt,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I am a little confused about the statement "blank set" you mentioned, it
will be more helpful if you could provide more specific description about
it. I assume that your application cannot get any data from the SQL Server
through the network of your company that used to work well. From the
information you provided, the only change that happened in you application
environment is the FDDI network took place of the formal 128K network,
right? If I have misunderstood, please feel free to let me know.
From my experience, you should confirm the connectivity of your computer
and the remote SQL Server first. You'd better use the Query Analyzer on
your computer and execute a simple query statement to see if the Query
Analyzer could retrieve any data from the remote SQL Server. This will be
helpful to confirm that both the remote SQL Server and the communication
between you computer and the remote SQL Server are working well.
Please let me know if the Query Analyzer could get data from the remote SQL
Server and I will provide further support based on the information you
provided. Looking forward to your response
Sincerely Yours
Baisong, Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
--
| Content-Class: urn:content-classes:message
| From: "Matt" <spam@.spam.com>
| Sender: "Matt" <spam@.spam.com>
| Subject: program return blank set
| Date: Wed, 19 Nov 2003 01:33:05 -0800
| Lines: 18
| Message-ID: <079801c3ae80$22a1ecb0$a301280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcOugCKhWxDbVERgQq6h62CYlPL2YA==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:317195
| NNTP-Posting-Host: tk2msftngxa11.phx.gbl 10.40.1.163
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi NGs,
|
| I have a program connecting to SQL Server as the back-
| end, recently, our company change to use FDDI instead of
| 128K network line; this causes the program return blank
| Set from the SQL Server. We don't modify the program, the
| only change is the networking situation. From 128k line
| to FDDI; the program worked fine for about 1 month before
| we made the change; FDDI also works great except this
| program. Do you have any idea?
|
| TIA.
|
|
|
|
|
|
|