Pass a Parameter(s) to SQL Agent Job

Question

I found a post claiming that it is possible to pass parameters to a SQL Agent Job, but no "how to" was included. Can someone explain how this is done?

In my scenario, I need a stored proc to invoke the SQL Agent and pass a parameter(s). The SQL Agent job, in turn, must pass the parameter(s) to an SSIS package step.

The alternative I've heard is to have the stored proc write values to a table, and then have the SQL Agent job (or the SSIS package it invokes) read those values from the table. I will take this latter approach if I must, though it is klugey.

Update: The motive for this exercise is to form an integration test consisting of (a) the SQL Agent job which provides a package configuration file and (b) the SSIS package which needs the values in the package configuration file. Thus I do not want to invoke the SSIS package directly. Furthermore, testers have neither permission to launch the SQL Agent job directly, nor should they be allowed to cause SQL Agent jobs to be created dynamically. The sproc (legally) circumvents the permission issue. Finally, the integration test can target one of dozens of SSIS packages, but it is not practical in my environment to have dozens of SQL Agent Job definitions. Ergo, a sproc launches the SQL Agent job, and the parameter indicates which SSIS package to launch.

Answer 1

Seems like you should be using the SSIS package configurations feature. Your stored proc can update the configuration value(s).