SSIS Data Conversion Transformation is similar to T-SQL function CAST or CONVERT. If you wish to convert the data from one data type to another data type then SSIS Data Conversion is the best bet. But, please make sure that you have compatible data in the column and don’t forget to change the column length for Varchar, NVarchar data types.
In this article, We will Export data present in the DimProducts Excel Worksheet to SQL Server database table by converting the data type from DT_NTEXT to DT_WSTR using SSIS Data Conversion. Below image shows the data present in the DimProducts Excel file.
SSIS Data Conversion Transformation Example
STEP 1: Drag and drop the data flow task from the toolbox to control flow and rename it as Data Conversion Transformation
Double click on it and it will open the data flow tab.
STEP 2: Drag and drop EXCEL Source, Data Conversion Transformation from toolbox to data flow region.
STEP 3: Double click on Excel source in the data flow region will open the connection manager settings and provides option to select the table holding the source data.
From the above screenshot you can observe that we are selecting the DimProducts.xls file present in our local hard drive
From the above screenshot you can observe that, we are selecting the Product Destination table present in the DimProducts.xls.
STEP 4: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
TIP: If we don’t want any column then there is no point to add it in to your SQL command.
Click ok and drag the blue arrow from Excel Source to SSIS Data Conversion Transformation. This will allow the transformation to use the source data.
STEP 5: Double click or right-click on the Data Conversion Transformation to edit and convert our source columns data type to required data type with length.
This is the table description we are using for the destination table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE [SSIS Tutorials] GO 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, CONSTRAINT [PK_DimProduct_ProductKey] PRIMARY KEY CLUSTERED ( [ProductKey] ASC ) ) GO |
We have to change our data types before we transfer data to the SQL server destination table to match Excel columns with the destination table.
NOTE: It is always necessary to convert data types while transfer from Excel to SQL server database.
STEP 6: Change the data type from DT_NTEXT to DT_WSTR and don’t forget to change the length.
Most of the times intelligence will automatically change the data type but sometimes we have to do it by our own.
Click ok
STEP 7: Now we have to provide Server, database and table details of the destination. So double-click on the OLE DB Destination and provide the required information
STEP 8: Click on Mappings tab to check whether the source columns are exactly mapped to the destination columns.
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
Check the results
Thank you for Visiting Our Blog
its very good and very helpful for me( am learning msbi)
is this the same we're doing in real time?
Please give/post/mail me the other transformations.
thankyou