SQL Server Integration Services (SSIS) supports two types of variables: system and user-defined. SSIS automatically generates the system variables when you create a package. That’s not the case for user-defined variables. You create them as needed when setting up your package. However, both variable types store data that tasks and containers can access during a package’s execution. In addition, package components can save data to user-defined variables during execution in order to pass that information onto other objects.

Variables are useful in a number of situations. You can bind them to Transact-SQL parameters in a Execute SQL task, or use them to provide the iterative lists necessary to run a Foreach Loop task. SSIS variables can also be mapped to the variables used within a Script task or Script data flow component. And anywhere you can create a property expression, you can include user-defined or system variables. Event handlers, too, can make use of both types of variables.

Viewing Variables in SSIS

To view the variables available in an SSIS package, click the Variables option on the SSIS menu. This will open the Variables pane in the left part of your window. By default, the Variables pane displays only user-defined variables when you first open it, so if none have been defined on your package, the pane will be empty. However, at the top of the Variables pane you’ll find several buttons that let you control what information is displayed:

• Add Variable: Adds a user-defined variable.
• Delete Variable: Deletes the selected user-defined variable.
• Show System Variables: Toggles between a list that includes system variables and one that does not. User-defined variables are blue, and system variables are gray.
• Show All Variables: Toggles between a list that includes all variables and one that includes only those variables within the scope of the package or the selected container or task. The list will include system variables only if the Show System Variables option is selected.
• Choose Variable Columns: Launches the Choose Variable Columns dialog box, where you can select which information is shown in the Variables pane.

Figure 1 shows the Variables pane after I clicked the Show System Variables button. As you can see, the pane now displays the system variables available in an SSIS package (SQL Server 2008 R2). Each listing includes the variable name, its scope within the package, its data type, and its pre-defined value. In this case, all the system variables have a scope of SsisVariables. That’s the name of the package I created, which means all the system variables listed in Figure 1 have a package-level scope and are available to the entire package.

Figure 1: Viewing the Variables pane in SSIS

I can display additional information about each variable by clicking the Choose Variable Columns button and selecting the columns I want to display from the Choose Variable Columns dialog box, shown in Figure 2. As you can see, the only columns I can display in addition to the default columns are Namespace and Raise Change Event (represented by the option Raise event when variable value changes). The Namespace column displays the User namespace for user-defined variables and System namespace for system variable. The Raise Change Event column indicates whether to raise an event when a variable value changes.

You can sort the variables listed in the Variables pane by clicking the top of a particular column For example, if you want to sort your variables by scope, you can click the top of the Sort column.

Creating User-Defined Variables in an SSIS Package

The Variables pane also lets you easily create user-defined variables. However, if you want to create your variables at a scope other than at the package-level, you should first add the necessary tasks and containers to your package. For this article, I created a simple package that includes only an OLE DB connection manager and an Execute SQL task. First, I added the connection manager and configured it to connect to the AdventureWorks2008R2 database on a local instance of SQL Server 2008 R2. I named the connection manager after the database.

Then I added the Execute SQL task, but before I configured it, I used the following Transact-SQL code to create the TestTbl in AdventureWorks2008R2 database:

The OLE DB connection manager and Execute SQL task will use a set of user-defined variables to insert data into this table. So let’s create those variables.

When you click the Add Variable button in the Variables pane, a row is added to the list of variables. (At this point, I usually find it easier to toggle the Show System Variables button off so that only user-defined variables are displayed.) Before you click the Add Variable button, however, you must make sure that the scope is preselected for your variable. That means you have to ensure that no containers or tasks are selected in your package if you want your variable to have a package-level scope. But if you want your variable to have a scope specific to a container or task, you must select that object in the SSIS designer before you create your variable. When you create a variable at the scope of a specific task or container, the variable is available only to that object and child objects, if it’s a container.

For our example package, the first variable I create has a package-level scope, which means no objects are selected. I click the Add Variable button to add a row to the list of displayed variables. I name the variable SqlServer, select the String data type, and provide the value localhost\SqlSrv2008R2, which connects to my local instance of SQL Server.

I then add two more variables. However, these are at the scope of the Execute SQL task, so I first select the task and then add the variables. The first of these two variables I name UserID, assign the Int32 data type, and provide a value of 101. The second one I name UserName, assign the String data type, and assign a value of johndoe.

Figure 3 show the three user-defined variable I added to the Variables pane. Notice that SqlServer variable has a scope of SsisVariables (the name of the package), and the other two variables have scope of Execute SQL Task (the default name of that task). The SqlServer variable will be used to pass in the name of the SQL Server instance to the connection manager. The UserID and UserName variables will be used to pass data into an INSERT statement in the Execute SQL task.

Figure 3: Adding user-defined variables to an SSIS package

When you create a variable in the Variables pane, you’re limited in your ability to view and update variable properties. However, you can view additional properties in the Properties pane. To view a variable’s properties, you must select that variable in the Variables pane, but also have the Properties pane open so you can view the variable’s properties. For example, when I select the UserID variable in the Variables pane, the Properties pane displays the properties shown in Figure 4.

Figure 4: Viewing a variable’s properties

Notice that there are more properties than what are shown in the Variables pane. For example, you can configure a user-defined variable to be read-only by setting its ReadOnly property to True. Or you can define an expression that determines the variable’s value. Properties you can’t modify are grayed out.

Using Variables in an SSIS Package

Once you’ve created your user-defined variables, you can reference them-along with system variables-in your package’s control flow and data flow components. One way to do that is to create property expressions that include the variables. For example, the SqlServer variable contains the target SQL Server instance, so I can use the variable to pass the instance name into the connection manager. To do so, I select the connection manager, ensure that the Properties pane is displayed, and create the following expression on the ServerName property:

The expression identifies the User namespace, followed by the SqlServer variable. Now when I run the package, the original value for the ServerName property is replaced by the current value of the SqlServer variable, as shown in Figure 5.

Figure 5: Adding a user-defined variable to a property expression

The original value of the ServerName property had specified the server’s actual name. After I ran the package, the variable value, which specified localhost, replaced the original value, as evidence by the ServerName and ConnectionString properties in the figure.

Now let’s turn to the Execute SQL task. On the General page of the Execute SQL Task editor, I specify the AdventureWorks2008R2 connection manager, as shown in Figure 6. That means, when the task connects to the target database, it will use the SqlServer variable specified in the connection manager’s property expression to establish that connection.

Figure 6: Configuring the Execute SQL task

Next, I add the following statement to the SQLStatement property:

The two question marks serve as parameter placeholders that we’ll map to the UserID and UserName variables. The mapping itself we do on the Parameter Mapping page of the Execute SQL Task editor, shown in Figure 7.

Figure 7: Mapping user-defined variables to parameters in the Execute SQL task

As the figure shows, I add two mappings based on the UserID and UserName variables, both as input parameters. Next I assign the appropriate data types, in this case, LONG and VARCHAR, respectively. I then set the parameter names (0 and 1) and leave the default value (-1) for the parameter size. When I run the package, the INSERT statement will add the variable values to the TestTbl table.

Now that you’ve seen how to use the user-defined variables, let’s take a look at an example that uses a system variable to insert data into the TestTbl table. To demonstrate this, we must first alter the TestTbl table. I used the following ALTER TABLE statement to add a DATETIME column to the table:

Now let’s add another parameter mapping to our Execute SQL task. In this case, we’ll use the ContainerStartTime system variable for the mapping, as Figure 8 demonstrates.

Figure 8: Mapping a system variable to a parameter in the Execute SQL task

Once again, we’re adding an input parameter, but this one is configured with the DATE data type, and the parameter name in this case is 2. But adding this mapping means we need to update our INSERT statement to include an additional parameter placeholder, as the following code shows:

Now when we run the package, it inserts not only the values from the UserID and UserName variables, but also from the ContainerStartTime variable, which contains a timestamp of when the Execute SQL task starts to execute.

One other item worth noting about the Parameter Mapping page is that you can also create a user-defined variable directly from the page. After you add a new mapping, select the New Variable option from the Variable Name drop-down list, as shown in Figure 9.

Figure 9: Creating a variable from within the Execute SQL task

When you select this option, it launches the Add Variable dialog box, shown in Figure 10. Here you can define a variable that can be used just like any other user-defined variable. Several tasks and containers offer this option, which is handy when you want to create a variable on the go.

Figure 10: Configuring a user-defined variable created in the Execute SQL task

One final example we’ll look at involves defining an equation that generates a value for a user-defined variable. But first, we’ll need to alter our table again. The following Transact-SQL code adds an INT column to the TestTbl table:

After I add the column, I create a variable named Runtime, configured with the Execute SQL Task scope, the Int32 data type, and an initial value of 0. I then create another parameter mapping in the Execute SQL task, as shown in Figure 11.

Figure 11: Mapping a user-defined variable to a parameter in the Execute SQL task

Of course, we can’t forget to update our INSERT statement to include an additional parameter placeholder, as the following code shows:

So now we have everything just about set up. However, if we run our package as is, all it does is insert a 0 into the Runtime column because that’s the value we initially assigned to the Runtime variable. What we really want to do is insert the amount of time-in milliseconds-it takes from the time the package starts running to the current date and time. To achieve this, we add the following expression to the Runtime variable properties:

 The DATEDIFF() method calculates the difference between the value in the StartTime system variable and the current data and time, as retrieved by the GETDATE() method. Note, however, to use an expression to generate a variable value, you must also set the variable’s EvaluateAsExpression property to True, as shown in Figure 12.

Figure 12: Using an expression to define the value of a user-defined variable

Now when a package’s components access the Runtime variable, a current value will be calculated and that value will be used.

Making Use of Variables

As the article has tried to demonstrate, variables extend the capabilities of an SSIS package to a significant degree; however, the examples I’ve shown here only skim the surface of how extensively they can be used. To see more examples of variables in actions, you might want to check out other articles that use variables:

• Adding the Script Task to Your SSIS Packages
• Working with Precedence Constraints in SQL Server Integration Services
• Working with Property Expressions in SQL Server Integration Services
• Implementing Checkpoints in an SSIS Package
• XML Configuration files in SQL Server Integration Services

Each article demonstrates ways in which you can incorporate variables into your SSIS packages. From this information and from what I’ve provided in this article, you should have plenty of examples of variables in action. As you’ll discover, they are, for the most part, easy to implement and can add enormous value to your SSIS packages.