Hello Everyone,

For one of the SSIS package, I'm processing .CSV files in a loop. I've used Sort transformation on Data Flow Task to order these files prior to pass them to the Table Difference component (COZYROCs tool). The first file has 22K records, second file has 200 records, third file has 7K records, forth file has 16K records, and last file has 100 records.

Now, the sort transformation works very quickly for first 3 files, but for the 4th one it is taking around 1 hr. time. Also, note that when this version of the package was running, everything on the server became very, very slow.

On the other hand, if I reverse an order of processing of these files, at this time, it takes excess amount of time for 22K records file for sort transformation.

Is it that the Sort transformation is buffering records, which needs to be cleared out after processing the records up to some limit or count?

Can anybody please advise how to get rid of this problem?

Any help would be much appreciated.

Hi Ankit,

Sort Transformation is a fully blocking transformation. It is a dam that lets nothing through until the entire volume of the river has flowed into the dam. Nothing is left to flow from upstream, and nothing has been passed downstream. Then once the transformation is finished, it releases all the data downstream. Clearly for a large dataset this can be extremely memory intensive. Additionally, if all the transforms in your package are just waiting for data, your package is going to run much more slowly. This is your scenario.

The solution is to sort the data from the source using an ORDER BY clause. But in your scenario, the solution should be open the csv file in Microsoft Excel, then sort the data and save the csv file. Then we should also have to tell SSIS that the data is sorted and how it is sorted. This is done in the Advanced Editor of your data source. First, set the IsSorted property of the sources output to true. Next, set the SortKeyPosition property of each column you specified in your ORDER BY clause; 1 for first, 2 for second, and so on. A SortKeyPosition value of 0 is the default and means that the column is not used in the sort. Thats all there is to it, using this method you will be able to sort your data so the SSIS transformations are happy, and can work without hours of waiting and memory hogging.

For more details about SSIS Non-blocking, Semi-blocking and Fully-blocking components, please see:
http://sqlblog.com/blogs/jorg_klein/archive/2008/02/12/ssis-lookup-transformation-is-case-sensitive.aspx

Thanks,
Katherine Xiong