By including the Merge transformation in a data flow, you can perform the following tasks:
- Merge data from two data sources, such as tables and files.
- Create complex datasets by nesting Merge transformations.
- Remerge rows after correcting errors in the data.
In this article , let us see an example for Merge Transformation .
Consider data from two sources – Flat file and Excel needs to be sorted & merged and then loaded into SQL Server table .
I added a Flat file & Excel in the path D:SourceFiles .
Create sample destination table :
CREATE TABLE MSSQLExperts(Id CHAR(5),Name NVARCHAR(50))
Drag&drop Data Flow Task on to the Control Flow tab and then double-click on Data Flow Task to get into the Data Flow tab .
In the Data Flow tab , drag&drop Flat File Source and Excel Source .
Create Flat File and Excel Connection Manager by pointing to the respective files .
In the Data Flow tab , drag&drop two Sort Transformation task from SSIS Toolbox .
One for sorting input from Flat File Source and other one for sorting input from Excel Source .
Settings in Sort Transformation task is as shown below :
In the Data Flow tab , drag&drop Merge Transformation task from SSIS Toolbox .
Settings in Merge Transformation task is as shown below :
Note : Before connecting input from Sort Transformation tasks to Merge task , right-click on both Source task – > Show Advanced Editor … – > Input and Output Properties – > Inputs and Outputs – > Output Columns – > Click on Columns – > Under Data Type Properties – > check both Sources has columns with same data type .
In the Data Flow tab , drag&drop OLE DB Destination from SSIS Toolbox and then Settings in OLE DB Destination task is as shown below :
After designing the package , Execute it and then query the destination table to check for the merged result set .