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 ...