SSIS Data Conversion Transformation

The Data Conversion transformation is like C# type conversions to convert the input data to the output data with different data type. If a data type cannot be converted to another data type, an error will occur. For details about SSIS data types, you can check it here.

In the following example, I'm going to load book information to a new table books in SQL Server Test database. An Excel file books.xlsx was created in C:\SSIS with the content in sheet1 like below.

[Book Info In an Excel File]

Now follow the steps below to create the sample package.

  1. Create a new package in the project LearnSSIS2 and rename it to DataConversion.dts.

  2. Drag and drop the Data Flow Task into the package and then click Data Flow tab.

  3. Create an Excel Connection Manager as the source connection like we did before.
  4. [Excel Connection Manager]


  5. Create an OLE DB Connection Manager localhost.Test as the destination connection to link to the SQL Server database Test.

  6. Drag and drop an Excel Source and an OLE DB Destination into the Data Flow working area and link them from Excel to OLE DB.
  7. [Excel To OLE DB]


  8. Right-click Excel Source and choose "Edit..." to open the Excel Source Editor. Then choose "Sheet1$" as the sheet of the Excel Connection Manager.
  9. pExcel Sheet 1]


  10. Click the Columns to check the External Column and Output Column.
  11. [Excel Source Editor Columns]


  12. Click OK button to close the editor. Then open the OLE DB Destination Editor and click "New..." to create the SQL as below.

  13. CREATE TABLE [books] (
        [ID] [int] identity(1,1) not null,
        [Name] varchar(100) not null,
        [Price] float not null,
        [BoughtDate] datetime null
    )
    

  14. Click OK to close "Create Table" window. Then click Mappings tab and set the Input Columns as below.
  15. [Column Mappings]


  16. Click OK to close the editor.
  17. But there is an error message displayed with a red cross icon. Put your cursor on the icon, an error message will be displayed as below.

    [Unicode Conversion Error]

    In this case, a Data Conversion transform is needed.


  18. Remove the link between the Source and the Destination and drag @ drop a Data Conversion transform between them and have them linked like below.
  19. [Data Conversion Added]


  20. Right-click the Data Conversion and choose "Edit..." to open its editor. Then Click "Book Name", change the data type to string and Length to 100 as follows.
  21. [Data Conversion Editor]

    Please be noted, the output name of the "Book Name" is called "Copy of Book Name" with string data type.


  22. Click OK button. Then open the OLE DB Destination Editor again and change the "Book Name" to "Copy of Book Name" in the Mappings tab.
  23. [Copy Of Book Name]


  24. Click OK button to finish the package.
  25. At last run the package and you will find the new table books is created and 4 records are added.

Now, it is time to review the package ImportExportWizard.dtsx which was created in Import Export Wizard section.

  1. Open the package and click Data Flow tab.
  2. You can see a Data Conversion transformation was created between the source and destination.


  3. Open the editor of the Data Conversion. You can see the conversions were created below.
  4. [Data Conversion In Wazard]


  5. Click Cancel button to close the editor.

In summary, Data Conversion transformation is used to convert a data type to another one in order to match its counterpart in the pipeline of the Data Flow Task.