Main differences:
Union All Merge
Sorted input: not requires requires
Number of inputs: unlimited only two
Sorted output: not generates generates
Union All Transformation is an SSIS transformation that combine multiple inputs into sigle output.
Merge Transformation is an SSIS conponent that conbines two sorted intups into a single output.
UNION ALL
Source data:
I will use 4 data sources. Two tables have 7 columns and 2 table have 5 columns. There will be some null values retrived.
Step 1:
Add 4 data sources
Step 2:
Add Union All transformation. The very first column in the Union All transformation [on the left side] is the OUTPUT COLUMN NAME. The columns on the right side from output are the input columns coming from the data sources. All data types must mutch. Some tables have missing columns, these values will be retrieved as a nulls.
You must match the input columns with the output column. If having different data types use Data Conversion conponent to match the data types.
Run the package.
Merge Transformation
Source data coming from SQL Table. Second table MatchedRecords has two extra columns.
Step 1:
Add input tables
Step 2:
Add Sort transformation. I have chosen ID column as the column to sort the data. You can pick multiple columns.
Step 2.1
Or you can sort the data on the server using SQL statement:
SELECT * FROM MatchedRecords
order by Id
Then right click on the data source Show Advance Editor for OLE DB Source > Input and Output Properties > Common Properties:
IsSorted: True
SortKeyPosition: 1 [under ID column]
Step 3:
If you place the source with extra two columns as a first Input you will be able to retrieve those 2 extra columns as well. If you would place that table as a Input 2 those two columns wont be displayed in the Input.
Table MatchedRecords has sorted input (Step 2.1), doesn't need Sort Trnasformation.
Step 4:
Run the package.
Comments