This is part 42 of the series of article on SSIS
In this article we are going to see on how to use the Data Conversion transformation inside a package. Data Conversion task 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 wrong 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/tutorial/ssis.php[more]
Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on 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 below image.
Here we are using a Flat file data source which has 3 columns as string. Out of the 3, we need to convert the data type of 2 columns. Let’s see on how to do that using this sample. Double click the Data Conversion control will open a window to do the configuration. Follow as it shows in the below image.
Here we can select the data type based on our need, like we can select a Currency Data type if an amount filed 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 below image.
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 to update to the database where it has constraints on having only numeric or a string based on the business.
So in this article we have seen on how to use the Data Conversion Transformation to transform a data of different type.