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.

3 thoughts on “Passing variables from SQL Agent to SSIS

  1. Hello,

    in my project the variable don’t will be set on the way above.
    (\Package.Variables[Control::strAgentInput].Value)

    This Property Path has worked

    \Package.Variables[User::TestString].Properties[Value]

    Visual Studio 2008 and SQL Server 2008 R2

    nice day helmino

  2. Hi, it’s an old ost but I’d like to crontribute; in order to set the path as a variable you’ll need put \” at the begin too and not only at the end, for example:
    \“C:\Program Files\Microsoft SQL Server\90\DTS\Packages\\”
    The way you use will work with only one variable but if you use more than one the package could fail.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>