Passing variables to a SSIS package from SQL Server

To run a package from the SQL Server Agent and pass in a parameter, Create a SQL Server Agent Job as usual. The Job Step Type is “SQL Server Integration Services Package”. In my example, the Package source is “File System”.

Select the package location and then move to the Command Line tab. Add the following to the command line:

/SET “\Package.Variables[User::myValue].Properties[Value]”;”Example\””

The job step window may look like:

Please note – there is a bug in some versions of SQL where if you save your job then attempt to edit the job steps command line, your changes do not take.

The only way that I’ve found to work around this is to script the Job (in SQL Server Management studio, right-click on the job and select ‘Script Job as -> Create To -> New Query Editor Window).

After the job is scripted, modify the @command parameter of the msdb.dbo.sp_add_jobstep stored procedure as required. Then re-name your job (right-click on the job and select Rename) and execute the script. This will re-create your job with the new command line. If the process fails you have the re-named job as a backup.