SSIS 2012 Script Component Streaming Error (0xC0047020)
It has been a while since I released a post, this has mostly been due to a recent involvement in some very time consuming scale-out architecture, there is nothing like building a whole SQL Server architecture from scratch, something very powerful and enterprise-y.
Anyhow the purpose of this post is to discuss one of the many issuesam finding with the new SSIS 2012. This error relates to error logging when having a Script Component task within a Data Flow task that is being used as a data “Source”, particularly when this Script Component is followed by a Lookup task, or any task that works on results In-Stream, as shown in the image above.
The error we are trying to resolve looks like this:
System.Runtime.InteropServices.COMException was caught
Message=Exception from HRESULT: 0xC0047020
I have created an SSIS Package that replicates the Script Component streaming to Lookup Task issue, the Data Source Script Component is actually very simple, and the CreateNewOutputRows looks like this:
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
int rowNo = 0;
for (int i = 0; i < 50000; i++)
Output0Buffer.RowID = rowNo;
Output0Buffer.AccountName = "test_acc";
Output0Buffer.Value = "0";
Nothing fancy at all here, the script task is followed by a Lookup task that takes the AccountName column and looks up a table called dbo.Account, returning the AccountID column of the dbo.Account table, the Lookup task is set to “Fail Component” when there is no matching entry. Again, nothing crazy here at all. Now again this is not an SSIS operational error, in my opinion this is simply extremely terrible logging.
I (now) know that the Lookup will yield no matching row, causing the component to fail, although this failure is not experienced by the Lookup task, but instead the data stream out of the Script Component task is what is failing, with a stack trace of:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100.AddRow(IntPtr ppRowStart)
This means that when am calling the AddRow method of the Script Component, the Script Component is failing. This is true, but this is not the source of the error, the actual failure is caused by the Lookup task not matching any rows. No where in the progress/execution of the package or even in the error thrown by the Script Component there is a reference to the Lookup task. Needless to say I had to spend hours trying to debug a Script Component that had issue at all, and infact it is what is consuming the resulting data-stream from the Script Component that has caused this failure.
Anyway, in order to resolve this issue, and hopefully to save you valuable hours that I have so dearly lost, you need to look beyond your Script Component, and debug whatever is consuming the data stream.
You can confirm that this is the issue by placing a non-streaming operator (such as an Aggregate task), which means that all the data will be streamed, and when the package fails, it will fail in the correct place.
Hope this helps some poor soul!
Crazy to think that SSIS is throwing an error downstream without even identifying the component correctly. Even for SSIS, that’s a bad one! Hopefully there’s an issue already open for this on MS Connect.
Thanks for posting – this was helpful. It is really a strange error. A Primary Key Violation error on the destination pops up the exception window.
Thankyou! I got the same error in SSIS 2008, and sure enough, truncation was occuring on a downstream derived column task. Once that was resolved, the issue disappeared.
The error I had was System.Runtime.InteropServices.COMException (0xC0047020)
No worries Tom, glad this helped you identify your problem!
Holy cow, yes, this could’ve been hours. As if SSIS doesn’t give me enough problems, at least this one was resolved without chasing my tail (or a wild goose, for that matter) for hours.
In my case, I was moving 500 columns from the source to the destination. The destination is 2 SQL Server tables. As we all know, a single row in a SQL Server table *cannot* be more than 8060 bytes, so if 250 columns per table has greater than 32 characters per column on average, then the destination blows up. And as a result, it was pointing the finger at my script, just like you said. What a relief.