How Build A Data Flow Task ( Transform data through SSIS )

You can transform data from one location(source) to another(destination) with adding modification to the data using Data Flow Tasks..To Create a Data Flow you need to select the Data Flow Task from the control flow panel and add to the control panel tab and double click on it . chose the appropriate Data Source and the Data Destination for the data transformation.

Data Source Can Be

* Data Reader Source – Extract data from relational DB by using .Net Provider

* Excel Source – Extract data from excel work book

* Flat File Source - Extract data from flat files

* OLE DB Source - – Extract data from relational DB by using OLE DB connection

Data Destination Can be

* Data Mining Model Training

* Data Reader Destination

* Dimension Processing

* Excel Destination

* Flat File Destination

* OLE DB Destination

I am going to discuss on OLE DB Source to OLE DB data transformation.

First drag and drop the OLE DB Source task and rename the name and description

Double Click the Task –

Select the OLE DB Connection Manager

Data Access Mode

Data Excrating Source – Which can be SQL command,Table/Veiw according to the data access mode you select

Always preview the table before closing the tab in order to avoid errors at the execution state

Then Drag and drop OLE DB Destination task and name it appropraiately

Before you do your mapping you should connect the Source and the destination first

For that you can just connect two task using OLE DB Source output(select both two packages right click -> add path-> OLE DB Source output)

Or

You can put any Data Flow Transformation task and then connect

Data Flow Transformation Tasks are show in the picture, you can use any of these task in-between and map to the destination

After mapping the source with the destination you can double click the Destination task and select the appropriate destination table from the connection manager and arrange the mapping accordingly