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 data type. Typically when dealing with connection properties, you will find they require a string value. You can convert a number to a string using the cast function DT_WSTR(<<length>>).
A common example of using expressions in connection managers is for importing a collection of flat files using the same package. You could use a Foreach Loop Container, (which is discussed in Lesson 43) to loop through a collection of flat files. You can create an expression on the connection manager to change the connection string during each iteration of the loop to the appropriate filename. To configure the Flat File Connection Manager to use expressions, you would follow the steps mentioned earlier in this lesson.
Try It
In this Try It, you create a flat file export that contains a count of employees in each department. The flat file you create should have the current date as part of the file’s name. After completing this lesson, you will understand how to use expressions in a connection manager.
You can download the completed Lesson34.dtsx from www.wrox.com.
Lesson Requirements
Create a package that uses the AdventureWorksDW2012 database and the DimEmployee table to load all the departments and a count of how many employees are in each to a flat file. Name the flat file EmployeeCount_(Current Date).txt, with the current date being populated by an expression after the underscore.
The date should be in the following mmddyyyy format: 06022012
NOTE You must have leading zeros when month or day is only one digit.
Hints
- With an OLE DB Source, show a count of all the employees grouped by their department using the DimEmployee table.
- Place the results in a flat file that has an expression on the Flat File Connection Manager’s ConnectionString property. The filename should have the current date as part of the name.
Step-by-Step
1. Create a new package and name it
Lesson34 or download the completed Lesson34.dtsx package from
www.wrox.com.
2. Drag a Data Flow Task onto your designer and name it DFT - Connection Expression.
3. Add an OLE DB Connection Manager that uses the AdventureWorksDW2012 database. Go to the Data Flow window and add an OLE DB Source in your Data Flow.
4. Open the OLE DB Source Editor by double-clicking the OLE DB Source. In the OLE DB Connection Manager field, select the connection manager you created in the previous step. Change the data access mode to SQL Command and enter the following SQL statement:
SELECT
DepartmentName
,count(EmployeeNationalIDAlternateKey)EmployeeCount
FROM DimEmployee
GROUP BY DepartmentName
Click OK to exit the OLE DB Source Editor.
5. Drag a new Flat File Destination into your Data Flow. Then select your OLE DB Data Source to expose the blue and red arrows. Drag the green arrow from the OLE DB Source to the Flat File Destination Task.
6. Double-click the Flat File Destination Task to open it. Click the New button to create a new connection manager. Ensure Delimited is selected in the Flat File Format dialog box and click OK. The Flat File Connection Manager dialog box opens. Name the new connection manager Employee Count. The filename should be C:ProjectsSSISPersonalTrainerEmployeeCount_.txt. Set the Format to Delimited and check the Column names in the first data row option. Choose Columns on the left side of the dialog box. The Column Delimiter drop-down should have Comma {,} chosen by default. Then click OK. Ensure the mapping is correct in the destination editor and then click OK again.
7. Click once on the connection manager named Employee Count and press F4 to bring up the Properties window. Click the Expression property once to display the ellipsis. Click the ellipsis to open the Property Expressions Editor, shown in
Figure 34-2.
8. Click the Property drop-down box and select ConnectionString. Then click the ellipsis next to the Expression property. This opens the Expression Builder.
9. Enter the following expression, shown in
Figure 34-3, which gives the desired results for a filename:
“C:\Projects\SSISPersonalTrainer\EmployeeCount_”+
RIGHT( “0”+(DT_WSTR, 2) Month(GETDATE() ), 2 ) +
RIGHT( “0”+(DT_WSTR, 2) Day(GETDATE() ), 2 ) +
(DT_WSTR, 4) Year(GETDATE() )+”.txt”
NOTE If you are copying the expression from an electronic copy of this document, you may have to redo the double quotes. Simply replace the special double quotes with plain double quotes. This is because Word and some other electronic versions of documents use special codes that the dialog editor does not understand.
Click the Evaluate Expression button to see the resulting string.
This expression is commonly used, so take a look at some important functions that are used here:
- Month(GETDATE() )—Returns the current month number.
- (DT_WSTR, 2)—Converts the month number to a string.
- RIGHT( “0“+(DT_WSTR, 2) Month(GETDATE() ), 2 )—Adds a 0 to every month, but displays only the last two digits. This is so months that already have two digits like December display only 12 instead of 012, and months with one digit like January display as 01.
Also notice that each file directory contains two backslashes, but only one is displayed when the expression is evaluated. A backslash is a special character in the SSIS expression language. To include a single backslash in your string (), you must use a double backslash (\). To learn more about special SSIS characters, you can search for “Literals (SSIS)” on MSDN, or go to
http://msdn.microsoft.com/en-us/library/ms141001.aspx.
Click OK to exit the Expression Builder and then OK again to exit the Property Expressions Editor.
10. The package is now complete and your destination filename is dynamic. Each day the package runs, it creates a new file with a different name that contains the current date. When the package is executed, your results will look like
Figure 34-4.
11. To confirm a good result, go to the C:ProjectsSSISPersonalTrainer folder. You should see the file created with the current date.
