March 2007
Beginner to intermediate
88 pages
1h 52m
English
Content preview from SQL Server Integration ServicesBecome an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
Start your free trial
Example 4: Data Type Conversion
The aggregate values in Figure 37 have data type of numeric (38, 19) while the underlying values have data type currency. We can change this using a data conversion transformation, which converts data in an input column to another data type and copies it to an output column.
Copy Example3.dtsx following the instructions at the beginning of Example 2: Aggregation. Rename it Example4.dtsx.
Open the Example4 package, and switch to the Data Flow designer.
Delete the data flow path from the Sum Amounts to Lookup Sales Person to make a place to insert the Data Conversion transformation into the data flow.
Drag the Data Conversion transformation from the Toolbox onto the data flow designer to the right of the Sum Amounts aggregate transformation. Rename the Data Conversion transformation to Convert Numeric to Currency.
Drag the output data flow arrow from Sum Amounts to Convert Numeric to Currency.
Double-click Convert Numeric to Currency to display the Data Conversion Transformation Editor dialog.
Select the SubTotal, TaxAmt, Freight, TotalDue, and Average TotalDue columns from the Available Input Columns. Change the Output Alias names to match the Input Column names. Change the Data Type for each to "currency [DT_CY]." The completed dialog is shown in the following figure.
Figure 39. Data Conversion Transformation Editor dialog
Click OK to complete the configuration ...