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!