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
asked Jun 28, 2011 at 04:23 AM in Default
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).
answered Jun 28, 2011 at 05:30 AM
Grant Fritchey ♦♦
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.
answered Jun 28, 2011 at 05:12 AM