Hello. within a data flow task I have two OLE DB sources that I am attempting to put into a merge join. 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. I understand what this means but I cannot see an IsSorted option in the properties of the OLE DB sources so I can't set it. Can you tell me where it is? for the time being I'm having to put a sort transform in to get it to work. Thanks

Go to the advanced editor of the OLE DB source; then to the input and output properties. Expand the OLE DB source output node and the output columns folder. There is a property at the column label called 'sortKeyPosition. Rafael Salas

The IsSorted property is on the Output, you then set the SortKeyPosition property on the columns within the output. You need both. Only set this if you actually have a sorted output, otherwise it will do nasty things. Read teh above in conjunction with Rafael's post though, this is all in the Advanced Editor.

Thanks Rafael and Darren.Even after I have set these two properties, it is still saying that the "IsSorted property is set to TRUE and Output Columns has Sort Key Position as ZERO" . This is not true. I am setting the property correctly and in order for all the columns. Any help?Raajakumari

Are you sure? I would double check that, and to be honest I have never seen SSIS falsely reporting that.Rafael Salas | Dont forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/

Thanks a lot Rafael, I really appreciate you responding to a post in a 3 year old thread.Into the issue, I rechecked it. It was my bad. I continued with the sorting numbers from first OLE DB Source to Second OLE DB Source. when I changed it, it went in fine.Now another issue. After that in the merge join, it is saying "Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata".What does this indicate? How to correct this?Raajakumari

I am assuming you are talking about a Merge join transformation, right?Merge join transformation requires to have both inptus sorted by the same columns you are using in the join. So, go back to the 2 source queires and make sure all columns used in the sort match across the 2 OLE DB source components.Rafael Salas | Dont forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/

Great! thanks a million Rafael,You are right on the dime. It worked. I greatly appreciate your help here.Another related question,After the merge join, do I have to use another OLE DB Source or shall I take the output of the MergeJoin into my OLE DB Destination?My package is running successfully and reading the exact record, but the OLE DB destination is NOT INSERTING ANYTHING INTO THE TARGET DB.Any insight?Raajakumari

If after the merge join you just need to insert the resulting rows, yes you should use an OLD DB Destination. As a best practice you set the destination to use fast load option and set the commit size and batch size to a number that is good for your server env. (hint: 10K is usually a good number)Rafael Salas | Dont forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/

Thanks Rafael,It worked and inserted the record.Eventhough I selected left join in the dropdown on the connection, I had to select left join on the merge join as well. Initially, I did not do that.Also there was a wrong mapping which merge join took it by itself. Once it is removed. It worked fine.Please stay on the lookout for more questions from me. I have a creepishly designed application populating the data into an operational DB and my job is to migrate it to another DB.Hence facing all kinds of SSIS road blocks. Need lot of help.Will be back with more.Again thanks a million!!!!Raajakumari

Hi in Sortkey position their is zero value, what value i should fill in that it is not taking 1/true please help regards Sunil Yogann