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

Chapter 29. Making a Connection Dynamic with Expressions

If you truly want to expand the possibilities of what you can accomplish with your packages, it is essential that you learn the SQL Server Integration Services (SSIS) expression language. One of the common uses for expressions in SSIS is to create a dynamic connection to allow packages to be altered by external or internal means.

In Lesson 17 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 17.

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 29-1. Find the Expression property and click the ellipsis (. . .). This action opens the Property Expressions Editor where you can select which property inside the connection manager you want to add an expression to. 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 can accept only one type of value, so often you have to cast the expression's value to the appropriate data type. Typically when dealing with connection properties you will find they require a string value using the cast function DT_WSTR(<<length>>) ...