As data may come from heterogeneous sources, there is often the need of converting data before the data is loaded into the destination. The problem can also rise when Unicode format is converted into non-Unicode format e.g. SSIS assumes all the data coming from Excel in Unicode format, and if the destination table is designed to store the data only in non-Unicode format, the execution fails as Unicode conversion fails to non-Unicode.

There is one point to remember, and that is that data conversion is done on column basis.

Having said that, this article focuses on how data can be converted in SQL Server Integration Service (SSIS). Let’s go through it step by step:

  1. Create a SSIS project, name it and save it locally.

    Create a SSIS project

  2. Drag and drop a excel source on dataflow tab in the middle.

    excel source

  3. Assign a name to the source and double click on it.

    Assign a name

  4. Assign a connection to Excel or create a new one depending on the excel version available.

    Assign a connection

  5. Browse the Excel file on the local system and map the columns. Make sure to select the check box to consider column names from first row:

    select the check box to consider column names

    Select excel file:

    Select excel file

    Choose the sheet from the workbook that contains data.

    Choose the sheet

  6. Drag and drop a data conversion from toolbox on to dataflow

    data conversion from toolbox

    Connect Excel data source and data conversion with the green arrow coming out from the Excel data source.

    Connect excel data source

    Double click on the data conversion control and map the columns. If necessary assign new names in the output column. Don’t forget to choose the appropriate conversion datatype from the dropdown. In my example, the destination table has column in non-unicode format and SSIS considers data in Unicode format from Excel, therefore, I select string datatype (DT_STR). Provide the length of the new column so that there is no data loss.

    data conversion

  7. Close the data conversion window and connect the green arrow coming out from Data conversion control to newly dragged OLE db destination.

    OLE db destination

  8. Create a connection manager or connect to the one available from the list.

    Connection Manager

    Configure

  9. Choose the destination table from the dropdown and go to mapping columns from the left hand side menu.

    *Choose the destination tabl

  10. Map column either on grid or by dragging and dropping arrows from available input columns to available destination columns. Make sure you chose the converted columns not the original one from the Excel sheet.

    dragging and dropping

  11. Save the project and execute it.

    Execute package

  12. Check the result in the SQL server if data is entered correctly.

    result
Read more articles on SQL Server: