SQL Server Integration Services supports variables, both system and user-defined. This enables an SSIS package to be truly dynamic in almost any way we want. The trick is mapping the variables to properties through expressions. Let’s take a look…
It’s simple enough to look at the variables in a package. Within the SSIS Designer, just click on the background of the Control Flow tab (to give focus to the package as a whole) and select the SSIS menu, then choose Variables. To add a user-defined variable to the package, click the “Add Variable” button. You specify a meaningful name, a scope, a data type and a default value. The scope defines where the variable may be used. If you have focus on the package, the variable will be available throughout the package indicated by the name of the package under Scope. If you have clicked on a container or task, then the name of that executable will appear under Scope and the variable will only be available for that executable.
It’s worth limiting the scope of a variable to only what is necessary but watch out, it’s easy to accidentally set the wrong scope as it depends on your current focus. If you make a mistake, you cannot change the scope, you have to delete and re-add the variable. To see the System Variables, there is a gray button for that. Also, you are only shown variables for the current scope. To see all variables in the package, click the blue “Show All Variables” button.
Now, as an example, say you wanted to run a package that connects to a database; however, you may want to change the server name at run-time. First of all, we define a variable for the server name with the package scope. Then we need to map the variable to the ServerName property. To do this, we right-click on the Connection Manager for the database and choose Properties then expand Expressions. Clicking the ellipsis button enters the Property Expressions editor. Choose the Property that you want to vary from the drop-down, in this case, ServerName. Then click another ellipsis button to enter the Expression Builder. You can use all sorts of functions and conditions to establish a property value but in this case we want a straight mapping to the ServerName property from the user-defined variable. Just find it under the Variables list then drag and drop it to the Expression area. For instance, the syntax looks like this: @[User::MyServer] where MyServer is the name of the variable.
Now, when you run the package, the variable value will be linked to the correct Property at run-time. To pass the run-time value to the package, you can use a Package Configuration that is built by the Package Configuration Wizard. Just use the SSIS menu again and choose Package Configurations…and proceed to add one. SSIS will look for the variable value in the selected Configuration at run-time. Like most things, it’s easy when you know how.