Passing variables from SQL Agent to SSIS

This is as much to remind myself as help everyone else out there, as I had to ring a former client to get the answer it took me a long to me to dig out last time I attempted this.

It is possible to pass a value from SQL Agent into a variable in SSIS. You need to predefine the variable within the package – assume it’s called strAgentInput in the Control namespace.

In the SQL Agent step that is calling the package, you’ll note a tab in the options called “Set Values“. To pass a value into a variable, enter in the Property Path column (including the leading backslash):

\Package.Variables[Control::strAgentInput].Value

Vary the namespace and variable name as per your package needs. You can also see the .Value property of the variable is being set here – you can set any other variable property here – but you must set a property. If you do not specify which property you are setting, nothing will get set.

To set the actual value, enter what you need in the Value column. What gave me so much trouble was entering a file path. The correct syntax for file paths is:

“C:\Program Files\Microsoft SQL Server\90\DTS\Packages\\”

Note the string is surrounded by double quotes and the final slash of the path is escaped by a backslash. Any other variation will lead to your string output having “/REPORTING E” tagged on the end of it. Which drove me insane trying to figure out. Hopefully i’ve saved you some pain.