Reader Level:
ARTICLE

Basic SSIS Data Transformation

Posted by Muralidharan Deenathayalan Articles | SQL Server May 13, 2011
In this SSIS series, I will start with basic data transformation.
  • 0
  • 0
  • 8120
Download Files:
 

Demo 1  -  Basic SSIS Data Transformation

Step 1. Create a new SQL Server Integration Services Project.

SSIS-Data-Transformation1.jpg 

Step 2. Create database connections by using Connection Manager.

SSIS-Data-Transformation2.jpg 

Step 3. Give your database server name, username, password and select database name.

SSIS-Data-Transformation3.jpg
 
Step 4. Drag Data Flow Task tool from Toolbox and place it in the control flow designer area.

SSIS-Data-Transformation4.jpg
 
Step 5. Edit the DataFlowTask

SSIS-Data-Transformation5.jpg
 
Step 6. Select ADO.Net Source and place it in the DataFlowDesigner surface.

SSIS-Data-Transformation6.jpg
 
Step 7. Select ADO.Net destination and place it in the DataFlowDesigner surface.

SSIS-Data-Transformation7.jpg
 
Step 8. Edit the ADO.Net destination source.

SSIS-Data-Transformation8.jpg
 
Step 9. Select the source connection manager and source table as mentioned below in the diagram.

SSIS-Data-Transformation9.jpg
 
Step 10. Connect the ADO.Net dataSource and ADO.Net Destination.

SSIS-Data-Transformation10.jpg
 
Step 11. Edit the ADO.Net destination and select the destination database and table and do the mapping of the columns.

SSIS-Data-Transformation11.jpg
 
Here the Name column is not mapped properly. So, what we can do is to create a new column by combining FistName + " " + LastName . To do that we have to use a DerivedColumn.  EmpID is the identity column in the destination database.

Step 12. Drag & drop the DerivedColumn component from the DataFlowDesigner tool surface.

SSIS-Data-Transformation12.jpg
 
Step 13. Change the dataflow as mentioned below.

SSIS-Data-Transformation13.jpg
 
Step 14. Edit DerviedColumn component.

SSIS-Data-Transformation14.jpg
 
Step 15. Edit the ADO.Net Destination Data component; map the Name column to Name column .

SSIS-Data-Transformation15.jpg
 
Step 16. Now save the package and run. Now the data gets transferred from source database to destination database.

Records in source table.

SSIS-Data-Transformation16.jpg
 
Datatransformation from Source database to Destination database.

SSIS-Data-Transformation17.jpg
 
Records in the Destination after the data transformation:

SSIS-Data-Transformation18.jpg

COMMENT USING

Trending up