I am building a data flow, where some tbales from more then one databases are merged using the merge join transformation. But at one point, after a merge join, I need to do another merge join. Therefore I need to sort the data again, or else you get thi "data is not sorted" warning in SSIS. But after I sort the result from the merge join, the output of the sort is empty. No rows are returned after that. I looked into my data, and I saw some NULL values in the column I sort on. So I put a derived column transformation after the merge, to replace the null values with another value. But still the sort tranformation gives no result. I selected all the colums on the input of the sort transformation to be passed through. Wat can be the cause of the sort transformation not giving back any rows?

how did you checked result of sort transform? try with data viewer and see what data exists on data path before sort , and after sort.http://www.rad.pasfu.com

That's just the issue. The result of the sort is nothing. No rows are sent through to the next step. While the input has 118 rows, which I have checked with the data viewer

Note that you don't need to sort output of merge join transform, because output of merge join is SORTED .http://www.rad.pasfu.com

True, but the output of the first merge join is sorted on the join key of that merge join. The next merge join has to take place based on another field. This field has sort order 0 (zero) in the output of the first merge join transformation, and I see no way to change this sort order, as there is no advanced edirot available for a merge join transformation

OK, That's fair enough didn't you checked the "remove rows with duplicate sort values" in sort transform?http://www.rad.pasfu.com

Nope. I did not check that option.Could it be that ssis wants to do more in the cache then my laptop van handle?

Nope. I did not check that option.Could it be that ssis wants to do more in the cache then my laptop van handle? I don't think so, could you give us a screenshot of running state of your data flow schema?http://www.rad.pasfu.com

Here it is: http://www.joostbazelmans.nl/ssis/ssis.jpg The problem is the Sort6 component at the lower right corner

everything seems correct! could you put another screenshot from SORT 6 transformation editor?http://www.rad.pasfu.com

That looks strange. Just to point one issue I read earlier. Reza even if remove duplicates would be applied one should get atleast one output. yes another screenshot is required to suggest anything. And how do you debug such a Data Flow task where no naming std is maintained? Tweet me..

Reza even if remove duplicates would be applied one should get atleast one output. Sudeep, you right, I just want to find starting point to catch the issue.http://www.rad.pasfu.com

I just want to find starting point to catch the issue. http://www.rad.pasfu.com yes everything looks fine and when all the other sorts are working fine there must be something wrong here. jbazel: Could you try deleting the Sort transform and resetting it up and see if the issue remains. Tweet me..

I put two more screenshots on my webspace: http://www.joostbazelmans.nl/ssis/ssis_sort_input.jpg. This is a screenshot from a dataviewer placed befiore the sort (the sort input) http://www.joostbazelmans.nl/ssis/ssis_sort_edit.jpg is the edit dialog box of the sort trasnformation that doesn't work I deleted the sort transform and added it again, but still no result

everything seems right, amazing. try with lookup instead of merge join in this step, I mean don't sort result of this merge join , use lookup transform after it, and set lookup table as tabel nsltypekontak... and set joining field ( this is not the solution, but a workaround )http://www.rad.pasfu.com

I have been playing around a bit more, and I decided to split this part of the transform into a seperate data flow. For that I first have to take care that I get the output from merge join 2.I did this with a OLE DB input as a SQL command. After that I put another OLE DB input in the flow. Both input are sorted in the advanced editor. Putting a merge join after these two sorted inputs did not work either. However, after putting a derived column after both OLE db inputs, that remove trailing and leading spacec, the merge work fine. The bad thing about it is that this trick did not work in the big flow. After putting the derived column transformation before the sort entries, it still didn't work. Because of a time issue, I am going ahead with the extra data flow noe. Thanks for thinking with me guys. I am glad you both are as suprised as I am :)