SSIS Data Flow Task Example

How to create Data Flow Task in SSIS packagem, extracting data from excel and loading into database table!

Data Flow task encapsulates the data flow engine that moves data between sources and destinations. In example below you will learn extracting data from excel sheet and loading into MS SQL server database.

Data Flow Task Design SSIS

What we want to achieve?
We want to extract data from excel sheet and insert into SQL table / tables.

In data flow we need three controls, like you can see at the picture below.

• Source - Excel (for setting up excel file location )
• Data Conversion - (for mapping columnns with right data type)
• OLE Destination - SQL

After setting up excel connection manager you can preview the data just by clicking on “Preview” button on control itself.

Now we need to set the data conversion control, because some excel data type may not be the same data type in SQL, so that may produce error at the time of insertion, and to fix that we take help of this data conversion control, which will allow us to change data type for any column, so now we can set the right SQL compatible data type.

As you can see in picture below, you can change data type of any column; not only type it also will allow to change the data length, suppose you have a column called “description” SQL table that accept 100 character length, but in excel, some field has data which is more than 100 character, to deal with such situation you need to fix the length of data, so only first 100 character will be taken for insertion, rest will be ignored.

Once you have set all above controls, simply run the package, make sure you have some data in excel file you have specified in control, you should be able to see all data are inserted into your SQL database table.