Demo 1 - Basic SSIS Data Transformation
Step 1. Create a new SQL Server Integration Services Project.
Step 2. Create database connections by using Connection Manager.
Step 3. Give your database server name, username, password and select database name.
Step 4. Drag Data Flow Task tool from Toolbox and place it in the control flow designer area.
Step 5. Edit the DataFlowTask
Step 6. Select ADO.Net Source and place it in the DataFlowDesigner surface.
Step 7. Select ADO.Net destination and place it in the DataFlowDesigner surface.
Step 8. Edit the ADO.Net destination source.
Step 9. Select the source connection manager and source table as mentioned below in the diagram.
Step 10. Connect the ADO.Net dataSource and ADO.Net Destination.
Step 11. Edit the ADO.Net destination and select the destination database and table and do the mapping of the columns.
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.
Step 13. Change the dataflow as mentioned below.
Step 14. Edit DerviedColumn component.
Step 15. Edit the ADO.Net Destination Data component; map the Name column to Name column .
Step 16. Now save the package and run. Now the data gets transferred from source database to destination database.
Records in source table.
Datatransformation from Source database to Destination database.
Records in the Destination after the data transformation: