The use of variables is vital to any type of program, and SQL Server 2005 Integration Services (SSIS) is no different. Tim Chapman describes how to use the built-in SSIS system variables inside your SSIS project.
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 PackageSystemAuditTo 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(
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
)
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 EINSERT INTO PackageSystemAuditFigure H(
PackageName,
CreationName,
MajorVersion,
MinorVersion,
VersionGUID,
MachineName,
CreationDate,
StartDate,
PackageID
)
SELECT
@PackageName,
@CreationName,
@MajorVersion,
@MinorVersion,
@VersionGUID,
@MachineName,
@CreationDate,
@StartDate,
@PackageID
To illustrate that my package has successfully entered auditing data into my PackageSystemAudit table, I can run the following SQL query:
SELECT * FROM PackageSystemAuditSee 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.