SSIS Data Conversion

SSIS Data Conversion Transformation is similar to SQL function CAST or CONVERT. So, if you wish to convert the data from one datatype to another, then SSIS Data Conversion is the best bet. But, please ensure that you have compatible data in the column, and don’t forget to change the column length for Varchar and NVarchar datatypes.

Here, we will Export data present in the DimProducts Excel Worksheet to the SQL Server database table by converting the data type from DT_NTEXT to DT_WSTR using SSIS Data Conversion.

The below image shows the records present in the DimProducts Excel file.

Excel Source 0

SSIS Data Conversion Transformation Example

STEP 1: Drag and drop the data flow task from the toolbox to control flow and rename it.

SSIS Data Conversion 1

Double click on it, and it will open the data flow tab. Next, drag and drop EXCEL Source and SSIS Data Conversion Transformation from the toolbox to the data flow region.

Please refer to CAST and CONVERT in SQL Server.

Add Excel Source 2

STEP 3: Double-click on the Excel source in the data flow region will open the connection manager settings. And it provides an option to select the table holding the source data. We are selecting the DimProducts.xls file present on our local hard drive.

Choose the Excel Source File 3

We are choosing the Product Destination table present in DimProducts.xls.

Select the Table from Excel File 4

Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.

Check the Columns and remove unwanted 5

Click ok and drag the blue arrow from Excel Source to SSIS Data Conversion Transformation. It will allow the transformation to use the source data.

STEP 5: Double click or right-click on the SSIS Data Conversion Transformation to edit and convert our source column’s datatype to the required datatype with length. Similarly, you can convert int to string.

SSIS Data Conversion Transformation Editor 6

It is the table description we use for the SSIS data conversion destination table.

CREATE TABLE [dbo].[DimProduct]
 [ProductKey] [int] IDENTITY(1,1) NOT NULL, 
 [EnglishProductName] [nvarchar](50) NOT NULL, 
 [Color] [nvarchar](50) NOT NULL, 
 [ListPrice] [money] NULL, 
 [DealerPrice] [money] NULL, 
 [EnglishDescription] [nvarchar](400) NULL, 
 [SalesAmount] [money] NOT NULL, 

  [ProductKey] ASC 

We have to change our data types before we transfer data to the destination table to match Excel columns with the destination table.

NOTE: It is always necessary to convert data types during the transfer from Excel to the SQL server database.

STEP 6: Change the data type from DT_NTEXT to DT_WSTR, and don’t forget to change the length.

SSIS Data Conversion 7

Most of the time, intelligence automatically changes the data type. However, sometimes we have to do it on our own. Click ok

STEP 7: Now, we have to provide the Server, database, and table details of the destination. So double-click on the OLE DB Destination and provide the required information.

OLE DB Destination Editor 8

STEP 8: Click on the SSIS Mappings tab to check whether the source columns mapped precisely to the destination columns.

SSIS Data Conversion 9

If you forget the conversion, it will fail because of the data conversion from DT_NTEXT to DT_WSTR. Let’s map the correct columns and Click ok to finish our SSIS Data Conversion package design. Let us run the package.

SSIS Data Conversion 10

Check the results

SQL destination table result 11

Comments are closed.