As stated on MSDN BOL :
The Merge transformation combines two sorted datasets into a single dataset .
By including the Merge transformation in a data flow, you can perform the following tasks:
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 :
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 .
The Merge transformation combines two sorted datasets into a single dataset .
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 :
USE AdventureWorks2012 GO 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 .
No comments:
Post a Comment