SSIS merge is one of the components of SSIS, available in the toolbox. SSIS merge works similar way to a SQL join it merges the two or more different sources (Sources can be of same type or different type / heterogeneous) into a single output. These two sorted data-sets can be anything like Flat file/Excel workbook/Relational table etc. SSIS is all about collecting data from different sources, if we want to merge the collected data from different sources then we can use merge component. Merge component accepts only 2 sorted (compulsory) inputs. If there are more than 2 inputs then it best to use Union All transformation component. Also, Merge transformation has only 1 output and does not have any Error output.
Taking Data flow task.
Double click on the data flow task.
In Data flow, I am taking two input source and configuring both sources.
Now I am taking Marge Transformation.
Now I am taking Merge Transformation and merging both sources into merge transformation
It is throwing an error.
It asks for sorting.
Now I am using sorting transformation. Let’s see.
Configure the sort transformation. And also configure the Merge transformation.
I am taking Multicast transformation to see the result.
Now I am executing this package.
See the data. It is in sorted order.
Let’s see if we use more than two inputs in Merge Transformation.
If both sources are the databases it will be better to sort the data in the database itself. It will be faster because all we know that Sort transformation is full blocking transformation. When we short data in the database also we getting the same error to sort data before Merge transformation. To overcome that issue we need to enable the IsSorted properties in the oledb source.
When we adding 3rd input in Merge transformation we are getting below error.
We can use only two inputs for the Merge transformation.
No comments:
Post a Comment
If you have any doubt, please let me know.