Sort Transformation Within a For Each Loop
Hi, I have an ETL package where I am using several data flow tasks within a for each loop. These data flow tasks in-turn have sort transformations. My package keeps failing after sometime reporting the following exception. Event Type: Error Event Source: SQLISPackage Event Category: None Event ID: 12550 Date: 1/19/2011 Time: 5:10:52 PM User: NT AUTHORITY\SYSTEM Computer: INBLRS4S01 Description: Event Name: OnError Message: The input buffer could not be cloned. An out-of-memory condition occurred or there was an internal error. Operator: NT AUTHORITY\SYSTEM Source Name: Sequence Container Source ID: {9A24345D-697D-4FC4-AA55-E45A43CA49B7} Execution ID: {AA30C64B-DBB1-46A8-ABAF-CBA9C66F607D} Start Time: 1/19/2011 5:10:52 PM End Time: 1/19/2011 5:10:52 PM Data Code: -1071611242 Have read here (https://connect.microsoft.com/SQLServer/feedback/details/127188/sort-memory-failure-in-ssis-with-foreach-loop) saying its a bug in sort transformation and it has been fixed. Please suggest a solution or a workaround. -Madhu
January 20th, 2011 2:04am

My source is XML. The package reads some xml files and loads them to tables. To traverse through the xml structure I need to join the tags -> to join I need to sort them. All data flow tasks are running sequentially.
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2011 2:17am

I am not sure if I can have staging tables. To access the XML elements in the innermost tag, I join the tags in a sequence for which they need to be sorted!
January 20th, 2011 2:33am

Thanks Nitesh. Tried doing what you said, but unfortunately it will be a HUGE change. Dont have that much time. Please suggest some workarounds if possible...
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2011 2:59am

Thanks Nitesh. Tried doing what you said, but unfortunately it will be a HUGE change. Dont have that much time. Please suggest some workarounds if possible... There is no workaround if you are using Merge join. you need to SORT. What you could check is how many joins are you doing. Are all of them required?
January 20th, 2011 7:37am

Yes all merge joins are required... Is there any other effecient way of reading elements from an xml without joining one elment to the next?
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2011 8:11am

How big is the XML file? You could use Script component to read the XML file using XML DOCUMENT and create the output directly without using the merges.
January 20th, 2011 9:10am

Thanks Sudeep. Each XML has upto 7 levels and a maximum of about 10,000 rows altogether.... I am not familiar with script component. Would love to try it if you dont mind explaining how to do it...
Free Windows Admin Tool Kit Click here and download it now
January 21st, 2011 12:12am

Taking your data into consideration I dont think XML Doc would be a good option. Could you send me the xml file I will provide you the code accordingly. Also let me know the output that you expect. rajsudeep<at>gmail<dot>com
January 21st, 2011 12:48am

Thanks Sudeep. Email sent.
Free Windows Admin Tool Kit Click here and download it now
January 21st, 2011 12:53am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics