Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 28. Making a Package Dynamic with Variables

Creating dynamic packages in SQL Server Integration Services (SSIS) enables you to have packages that can reconfigure themselves at runtime. Using variables is a significant part to making a package dynamic because with variables you are able to pass values directly into the properties of individual components.

A variable is essentially a placeholder for a value within a package that you can write to or read from as many times as you want. A process within the package can read the variable's value and use it or write to the variable and change the value directly.

Variables come in two forms: system variables and user-defined variables. System variables are predefined and include things like the package name and package start time, whereas user-defined variables are created solely by the developer.

To create a user-defined variable, simply right-click the design surface in Business Intelligence Development Studio (BIDS) and click Variables. This action opens the Variables window where you can create a new variable by clicking the icon in the top left. Once you create a new variable you need to populate the fields Name, Scope, Data Type, and Value.

All variables must be assigned to a specific access level in a package called scope. The scope can be set to an individual component so it is available only to that object, or it can be set to the package level so it can be used anywhere in the package. For example, in Figure 28-1 the variable ...