Microsoft Integration: Data type Conversion in an SSIS

Introduction: 

Whenever we want to perform some analysis on data then first we have to import that data in our database and before importing that data we have to change the format of data in an appropriate format according to our database.

In this project, we would learn how to change data format in SSIS.

Aim: 

To change the data format of Flat file and import that data in our SQL server database.

Tools required:

• SSIS tool

• SSMS tool.

• Flat file source.

Step to perform this task:

Step 1:

Create a package with appropriate name and a SQL database table to store your file data.

Step 2:

Add data flow task in Control flow base window and double click on it.

Add the Data source and Data destination and configure them with the data file and data destination database table.

Step 3:

Add a data conversion task from    SSIS Tool > Common > data conversion 

 Step 4:

Now make connection from   Data Source ---> Data Conversion ---> Data destination.

And double click on data conversion, a window will pop up.

Window: Data conversion transformation Editor

Here you can transform data for each and every row you select.

Add column which you want to convert and Change their Data types and Length.

You can change the name of out column also and click ok after it all done.

Step 5:

Now double click on data destination task, a new window (OLE DB destination editor) will pop up.

Go to 

OLE DB destination editor> mapping> change mapping between Available inputs to Available destination.

And then click ok.

Note: connect Available destination with new outputs from data conversion task output.

Now everything we come up with no error sign.

Step 6:

Start debugging by clicking F5 button or clicking on the play button to start the package.

All your data will be converted in proper format and imported destination table.