question

aRookieBIdev avatar image
aRookieBIdev asked

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
ssisjobvariables
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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).
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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 ?
0 Likes 0 ·
Yeah, that's where you can set them, but to modify them dynamically you have to use variables and adjust the variables.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.