x

Values to Jobs

Hi,

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 '11 at 04:23 AM in Default

aRookieBIdev gravatar image

aRookieBIdev
2.3k 42 54 58

(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 '11 at 05:30 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

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 '11 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 '11 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 '11 at 05:12 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x900
x85
x19

asked: Jun 28 '11 at 04:23 AM

Seen: 714 times

Last Updated: Jun 28 '11 at 04:23 AM