DATA CONVERSION TRANSFORMATION

Introduction:-

            The data conversion transformation takes input columns and creates a copy of that column with selected data type.

            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 the above example we should convert the source column data type to target column data type then we can avoid data conversion errors.

 

 SQL Server to SSIS data type:

           

SQL server	SSIS
smallint	two-byte signed intger (DT_I2)
int	four-byte signed integer (DT_I4)
bigint	eight-byte signed integer (DT_I8)
float	double-precision float (DT_R8)
char,varchar	string (DT_STR)
nchar,nvarchar	unicode string (DT_WSTR)
decimal,numeric	numeric(DT_NUMERIC)
decimal	decimal (DT_DECIMAL)
smallmoney,money	currency (DT_CY)
date	database date (DT_DBDATE)
datetime	database timestamp (DT_DBTIMESTAMP)

Introduction:

http://msdn.microsoft.com/en-us/library/ms141704.aspx

Sample package:-

Requirement:

I having two tables (Sample_source and Sample_target) one is source and other one is target table shown below.

Source table column names and data types:

S.Number	Field name	Data type
1	ID	varchar(10)
2	Name	Varchar(30)
3	Price	varchar(10)

Target table column names and data types:

S.Number	Field name	Data type
1	ID	int
2	Name	varchar(30)
3	Price	Decimal(9,4)

I want to load the data from source to target.

Package creation:

• In control flow items, drag and drop “Data flow task” into designer surface.
• Right click on “Data flow task” and select edit option then it will go to “Data flow”
• From data flow items select “OLEDB source”, then drag and drop into designer surface. Edit the "OLEDB source" and create the source table mappings.

  

• Once source mappings are finished, drag and drop the “data conversion” transformation into designer surface.
• Select the “OLEDB source” green arrow and connect to the “Data conversion” transformation.
• Right click on the “Data conversion” transformation and select edit option.
• “Data conversion transformation editor” window will be open, shown below.

 

• Choose the required columns by selecting the check box. I want to do data conversion for ID and Price columns because target data types are different for those two columns.

• Selected id and price columns data types are string. we need to convert the id column as “int” and price column as decimal (9,4) by using "Data type" drop down list, shown below screen shot.

• Click on “OK” button and then "Data conversion transformation editor" window will be disappear.
• Drag and drop the “OLEDB destination” into designer surface.
• Select “Data conversion” transformation green arrow and connect to the “OLEDB destination”.
• Edit the “OLEDB destination” then “OLEDB Destination Editor” window will open, give the connection manager and table name. Select mappings option and map the source (converted) columns to target columns by using drop down option shown in below screen shot.

 

 

• Press “OK” button.
• Package mapping is finished, execute the package.