Yay! There’s a GUI to execute SSIS packages!

Boo! It’s really hard to figure out how to pass variables through it!

You may have seen a message that looked something like this:

TITLE: SSIS Execution Properties
——————————

DTExec: Could not set Package.Variables[User::MyVarA].Value value to Data Source=DevServ1.

——————————
BUTTONS:

OK
——————————

Here are the three most common mistakes people make when trying to use the Run Package (aka Execute Package Utility) GUI to set variables for Microsoft SQL Server Integration Services (SSIS):

1. Not including a forward slash before the variable name.
2. Including quotes around either the variable name or the value.
3. Failing to “fully qualify” the variable name.

The lack of detail/instruction on how to use this feature, both in the GUI and online is always surprising to me.

Alright here goes:

1. Get the name of the variable you want to set from your SSIS package. (The easiset way to do this is open the package in Visual Studio). For the example we’ll assume our variable is called MyVarA
2. In SSMS (SQL Server Management Studio) object explorer right click on the package you want to run and choose “Run Package”.
3. Select “Set Values” from the menu on the left.
4. Click in the box directly underneath the lable “Property Path”.
5. Enter your variable name in the following format: \Package.Variables[User::MyVarA]
6. Side note: you can also add .Value to the end like this \Package.Variables[User::MyVarA].Value
7. Enter the value for you variable. You do not need to include any escape characters. They will be handled automatically.
8. Click execute!

Here’s a screenshot showing what a my example looks like:

I hope you found that helpful! If you were making any other mistakes on getting this to run please leave a comment and let me know and I’ll add it to this post.