Values to Jobs


I have a situation where I am executing a package with variables using JOB. I need to dynamically pass the package variable values when I am calling the JOB. Is there any way to do it other than changing the values in the sysjobsteps and calling the Job.

Thanks in advance Kannan

more ▼

asked Jun 28, 2011 at 04:23 AM in Default

avatar image

2.8k 56 65 71

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

One approach that comes to mind, store your values in a table, query the table to get the information and then pass that on to another set of commands. You can wrap the whole in a SQL Command script and call that from SQL Agent. Do the same approach using PowerShell too (that'd be my preference).

more ▼

answered Jun 28, 2011 at 05:30 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

While Job creation, I see a tab called "setvalues" Was wondering if this could be of any use. Is it just used to set values while step creation ?

Jun 28, 2011 at 11:13 PM aRookieBIdev

Yeah, that's where you can set them, but to modify them dynamically you have to use variables and adjust the variables.

Jun 29, 2011 at 04:33 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

What means dynamically? I mean how you determine, what value to pass to the package? You want to put some values manually or you have some logic behind the scenes?

You can eg. update the package to use parent package variables for configuration and create a master package for the sub package. The master package retrieves appropriate values and then executes the child package whose variables are automatically configured by the values from the master package.

Other way could be retrieve the values dynamically directly in the package from configuration.

But if you want simply run the job manually with different sets of values, then there will not be other way than modifying the jobstep properties.

If you really need to execute the package with manually provided values, then the easies way wold be to create a script or bat file which will take appropriate parameters and execute the dtexec directly with appropriate settings.

more ▼

answered Jun 28, 2011 at 05:12 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 28, 2011 at 04:23 AM

Seen: 1010 times

Last Updated: Jun 28, 2011 at 04:23 AM

Copyright 2018 Redgate Software. Privacy Policy