There are a number of ways that you can incorporated variables into your SSIS Scripts. Robert Sheldon demonstrates how.
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: Viewing the Variables pane in SSIS
Figure 2: Selecting columns for the Variables pane
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:
USE AdventureWorks2008R2;
GO
IF OBJECT_ID('dbo.TestTbl') IS NOT NULL
DROP TABLE dbo.TestTbl;
GO
CREATE TABLE dbo.TestTbl
(UserID INT, Username VARCHAR(50));
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.GO
IF OBJECT_ID('dbo.TestTbl') IS NOT NULL
DROP TABLE dbo.TestTbl;
GO
CREATE TABLE dbo.TestTbl
(UserID INT, Username VARCHAR(50));
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:
@[User::SqlServer]
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:
INSERT INTO TestTbl
VALUES(?,?)
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.VALUES(?,?)
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:
ALTER TABLE TestTbl
ADD StartTime DATETIME;
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.ADD StartTime DATETIME;
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:
INSERT INTO TestTbl
VALUES(?,?,?)
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.VALUES(?,?,?)
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:
ALTER TABLE TestTbl
ADD Runtime INT;
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.ADD Runtime INT;
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:
INSERT INTO TestTbl
VALUES(?,?,?,?)
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:VALUES(?,?,?,?)
DATEDIFF("ms", @[System::StartTime] , GETDATE())
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
No comments:
Post a Comment