Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Section 4

Making Packages Dynamic

• Lesson 32: Making a Package Dynamic with Variables
• Lesson 33: Making a Package Dynamic with Parameters
• Lesson 34: Making a Connection Dynamic with Expressions
• Lesson 35: Making a Task Dynamic with Expressions

Chapter 32

Making a Package Dynamic with Variables

Your packages will be more flexible and more useful if they are dynamic. Dynamic packages in SQL Server Integration Services (SSIS) can reconfigure themselves at run time. Using variables is one of the ways you can make a package dynamic and reusable. You can use variables to set properties of components, parameters for T-SQL statements and stored procedures, in script components, in precedence constraints, and many other places. A variable is essentially a placeholder that has a name, data type, scope, and value. You can read and change the value of your variables within your package. 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. You cannot create system variables, but you can read them. User-defined variables are created solely by the developer.

To create a user-defined variable, simply right-click the design surface in SQL Server Data Tools (SSDT) 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, Data Type, and ...