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.

  1. Copy Example3.dtsx following the instructions at the beginning of Example 2: Aggregation. Rename it Example4.dtsx.

  2. Open the Example4 package, and switch to the Data Flow designer.

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

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

  5. Drag the output data flow arrow from Sum Amounts to Convert Numeric to Currency.

  6. Double-click Convert Numeric to Currency to display the Data Conversion Transformation Editor dialog.

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

    Data Conversion Transformation Editor dialog

    Figure 39. Data Conversion Transformation Editor dialog

  8. Click OK to complete the configuration ...

Get SQL Server Integration Services now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial