Using system variables in SQL Server 2005 Integration Services applications

The use of variables is vital to any type of program, and SQL Server 2005 Integration Services (SSIS) is no different. SSIS has been drastically improved over DTS in SQL Server 2000 to provide much better support for the use of variables. This tutorial describes how to use the built-in SSIS system variables inside your SSIS project.

Variables

Variables are programming constructs that allow you to change program elements at program runtime. There are two types of variables in SSIS: system variables and user-defined variables. System variables are built into SSIS and contain system- and package-related information. User-defined variables are created by the SSIS developer. (I will take a look at the different types of variable scopes in a future SSIS article.)

Example of how to use SSIS

The best way to understand how to take advantage of system variables inside your SSIS applications is to see an example. The example makes use of SSIS system variables to create a simple package auditing. Auditing your SSIS packages allows you to keep track of the successes, failures, and potential performance degradation over time.

First, I want to create the table that I will use to store the auditing information. This is the script I use to create my auditing table:

CREATE TABLE PackageSystemAudit
(
       PackageAuditID INT IDENTITY(1,1) PRIMARY KEY,
       PackageName VARCHAR(255),
       CreationName VARCHAR(255),
       MajorVersion INT,
       MinorVersion INT,
       VersionGUID UNIQUEIDENTIFIER,
       MachineName VARCHAR(255),
       CreationDate DATETIME,
       StartDate DATETIME DEFAULT(GETDATE()),
       EndDate DATETIME,
       PackageID UNIQUEIDENTIFIER
)

To access the variables that are available in an SSIS package, right-click in the Control Flow or Data Flow screens and select the Variables option from the pop-up menu. (Figure A) Figure A

Selecting the Variables option presents a Variables window that includes all of the system- and user-defined variables that currently exist in the package. Figure B lists all of the system variables that I have present in my SSIS package. Figure B

To insert data into my auditing table, I need an Execute SQL Task added to my Control Flow window from the Control Flow menu. An Execute SQL task will connect to a database server and execute the defined SQL statement contained inside of the task. (I will work exclusively within this task for the remainder of this example.) To add the Execute SQL Task to your solution, simply drag it onto the Control Flow menu from the toolbar. (Figure C) Figure C

Figure D is the Execute SQL Task Editor dialog box. One option that I want to change in this window is the ConnectionType option. In previous examples, I always use the default option OLE DB; for this example, I use ADO.NET because it allows me to use SQL Parameters inside of my SQL statement in the manner in which I prefer. I can accomplish the same result with the OLE DB connection type, but the implementation will differ. Figure D

Once I have my ConnectionType set, I need to set the connection properties. To do so, I click in the Connection drop-down box, and select <New Connection>, which will prompt the Connection Manager.

In the Connection Manager screen, I set the Server that I wish to connect to, along with the database that I will use. I need to make sure that the database I select is the same database that I created in my PackageSystemAudit table. In this case, the database name is TimChapman. (Figure E) Figure E

Once I establish my connection information, I can move on to mapping my system variables to my SQL Server parameters. The screenshot below shows the Parameter Mapping screen before any parameters have been mapped. (Figure F) Figure F

Under the Variable Name section, I select the system variables that I want to map to SQL parameters, which I will later use to insert into my auditing table. I also need to define the Direction of the SQL parameter (these are all Input parameters), along with the data type, and the SQL Parameter name. I will use the system variables and SQL parameters defined below as data inputs in my auditing table. (Figure G) Figure G

At this point, my database connection is established, and my system variables are mapped to my SQL parameters. All that is left is to construct the SQL statement that I will use to insert the data in the SQL parameters into the PackageSystemAudit table. To do this, I need to click the ellipsis (...) next to the SQLStatement text box and type in the INSERT statement that I will use to add the data. Figure H shows the SQL Query box, along with the SQL statement that I used to insert the data.

INSERT INTO PackageSystemAudit
(
       PackageName,
       CreationName,
       MajorVersion,
       MinorVersion,
       VersionGUID,
       MachineName,
       CreationDate,
       StartDate,
       PackageID
)
SELECT
       @PackageName,
       @CreationName,
       @MajorVersion,
       @MinorVersion,
       @VersionGUID,
       @MachineName,
       @CreationDate,
       @StartDate,
       @PackageID

Figure H

To execute the package, I can right-click the Execute SQL Task icon and select Execute Task, or I can hit [F5], or I can press on the green arrow on my toolbar. Figure I is a screenshot of the successful execution of the package. Figure I

To illustrate that my package has successfully entered auditing data into my PackageSystemAudit table, I can run the following SQL query:

SELECT * FROM PackageSystemAudit

See Figure J. Figure J

Limited scope

While there is a lot of power in using system variables, the scope of what you can accomplish with them is somewhat limited.

Next week's article will illustrate the power of using user-defined variables inside your applications. I'll also discuss how to set the data in user-defined variables at runtime and use later in the execution context of the package.

Additional resources about Integration Services

• Execute SQL statements in Integration Services
• Handle conditional data and data conversion using SQL Server 2005 Integration Services
• How to import an Excel file into SQL Server 2005 using Integration Services

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.