Data Conversion Transformation

- July 26, 2014

The data conversion transformation takes input columns and creates a copy of that column with selected (new) data type. If source columns data types are not matched with target columns, some cases SSIS will throw validation/failure error.

SQL Server to SSIS Data Type:

SQL server	SSIS
Smallint	Two-byte signed integer (DT_I2)
Int	Four-byte signed integer (DT_I4)
Bignt	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)

Step 1:

Click Windows button and go to SQL Server 2008 R2 and run as a administrator.

It shows a bellow window.

Step 2:

Go to a views and create a new project ( Short cut of New Project is Ctrl + Shift + N).

Step 3:

Give a project name (e.g. IS) and click OK button and then shows a bellow window.

Step 4:

To drag and drop the Data Flow Task

Step 5:

To edit the Data Flow Task then it open a Data Flow

Stem 6:

To dag and drop the OLEDB source and edit it.

Step 7:

To give a OLEDB connection and select the table from given data base and select which columns are required.

Step 8:

To configure the data conversion double clicks on Transformation.

Step 9:

Check the available input columns for which the data type will be changed. We can alias the input columns also. Select the compatible data type in Data type column and click on OK.

Step 10:

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.