Data Conversion Transformation

The Data Conversion transformation converts the data in an input column to a different data type and then copies it to a new output column. Data Conversion is used to change the data type of a column. For example, a package can extract data from multiple sources, and then use this transformation to convert columns to the data type required by the destination data store. You can apply multiple conversions to a single input column.

Create new integration project.

In control flow item select Data flow task.

Double click on the data flow task.

Here I am going to migrate data from excel to sql server. I means source as excel and destination is sql server. If source columns data types are not matched with target columns, some cases ETL will throw validation/failure error. Example: Source column is having “Varchar” data type and target is having “Integer” data type. In this case ETL will throw validation/failure error.

In data flow I am taking data flow source as Excel source. After that I make the connect.

Now we need to take data conversion transformation.

Double click on Data Conversion

Select the column name which one you want to change the data type.

A: - It will show the all available columns which is in source. Select the columns name which one you want to convert the data type.

B: - It will show the selected input columns name.

C: - It will show the Alias of the output column name.

D: - Here you can select the data type of the output column.

E: - you can define the length of the column.

 F: - After complete click ok.

Now take Oledb Destination.

Create the connection. While mapping the columns select the converted columns name.

Click ok.

Now Build the project and execute the package.

Package Executed successfully.