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

Chapter 34

Making a Connection Dynamic with Expressions

To expand what you can accomplish with your packages, it is essential that you learn the SQL Server Integration Services (SSIS) expression language. A common use for expressions in SSIS is creating dynamic connections. For example, this enables you to change an output file name or change the database connection while moving a package from test into production without having to reopen and edit the package. You may change any available property using an expression.

NOTE In Lesson 21 you learned about the Derived Column Transform and many of the common functions used in expressions. This lesson focuses on using expressions in connection managers, so if you want a recap on the expression language itself, refer to Lesson 21.

To configure a connection to take advantage of expressions, select the connection manager and press F4 to open the Properties window, as shown in Figure 34-1. Find the Expression property and click the ellipsis (...). This action opens the Property Expressions Editor where you can select the connection manager property you want to define with an expression. Once you have selected the property from the drop-down box, click a second ellipsis in the Expression property to open the Expression Builder. Here you can begin building your expression for the given property you have selected.

Remember that each property has a specific data type, so you often have to cast the expression’s value to the appropriate ...