IsSorted properties in SSIS

The Merge Join Transform in SSIS is a great way to load a Data warehouse quickly and an easy way to join two data sources together. There are a few requirements to join these two data sources. The data sources must be sorted and there must be a key that we can join them with. This can all be done in the data flow of the SSIS package.

All we know that sort is full blocking transformation. If our sources are sql server then, why not we sort it on the database itself. If we sort the data in database then we no need to use sort transformation but when I connect them to the merge join transformation I get the following error: The IsSorted property must be set to true on both sources of this transformation. 

See the example

I am taking two data Sources and doing the merge join. Below is design

 

Creating the required configuration of OLEDB source (both source)

Before merge we must to sort the data.

 

Now I am sorting the data in sql script and let’s see the result.

I have sorted data in sql server itself.

But still we are getting the same error

 

For overcoming this issue we need to tell the Merge transformation that incoming data in sorted.  With the help of the IsSorted properties in OLDBD source itself.

Let’s see how we can set these properties.

Right click on the OLEDB source

 

Select the Show Advance Editor

 

Go to Input and Output propertiesà OLEDB Source Output à IsSorted

Select the IsSorted properties true. After that Go to Output Column

 

Select the ID (I am sorting the data base on the ID so I am selecting the ID as sortKeyPosition as 1) and set the SortKeyPosition 1.

I am setting these properties for Both OLEDB. Once we set these properties the error has gone from the Merge transformation.

Double click on the Merge Transformation and do the mapping.

Click ok.

For seeing the result I am taking Multi task.

 

Now I am executing the package.

 

Package is executed successfully.

Sort Transformation is full blocking transformation. If our source the sql server, oracle or any database the good idea to do the sorting in database it. It makes our package faster.