Monday, 12 September 2016

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.

4 comments:

  1. Thank you so much for providing information about SSIS and other such aspects of IT which helps in solving many complex IT problems. SSIS Upsert

    ReplyDelete
  2. Whether you are looking for an HP laptop repair or a HP service center, you can find one in Patel nagar with the help of us you can find a wide variety of repair facilities nearby. The list may contain popular brands or centres. Based on the parameters that you specify, you can choose the HP repair center that meets your needs best.
    hp service center in patel nagar

    ReplyDelete

If you have any doubt, please let me know.

Popular Posts