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