SQL Server Integration Services (SSIS) - Data Conversion Transformations in SSIS

Introduction:

In this article we are going to see how to use the Data Conversion transformation inside a package. A Data Conversion task is mainly used in large transformations where the data compatibility should be checked in order to update back to the database. Say for example we get some daily morning loads as some file and in that file we can see some data type errors which needs to be altered from string to integer; in that case this task can be used. Let's jump start to the section on how to do that using a sample package.
You can look into my series of article on SSIS at the url - http://f5debug.net/all-articles/

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on an integration services project. Once the project is created, we will see how to use the Data Conversion control. Once you open the project just drag and drop the Data Conversion control and a source and destination provider as shown in the image below.


Here we are using a Flat file data source which has 3 columns as strings. Out of the 3, we need to convert the data type of 2 columns. Let's see how to do that using this sample. Double-clicking the Data Conversion control will open a window to do the configuration. Follow as shown in the image below.


Here we can select the data type based on our need, like we can select a Currency Data type if an amount field is found. Now click on the OK button to complete this step. Now we need to drag and drop a destination file task and configure as shown in the image below.


Now press F5 to run the task and you can find the data type converted. To have it used in real time we need to use it to update the database where it has constraints on having only numeric or a string based on the business.

Conclusion:

So in this article we have seen how to use the Data Conversion Transformation to transform a data of different type.