0

I have multiple packages that I'm dealing with but the problem comes up with this Control Flow:
enter image description here

The first Script tasks above the union basically take data from in-memory Recordsets and make them available to the Union All task. This is because I don't know of any other Source task that can read from Recordset objects. They're all of the exact same structure.

The problem is with the ADO.Net Destination task and the error message is:

"An exception has occurred during data insertion, the message returned from the provider is: Received an invalid column length from the bcp client for colid 10. "

I found this SO post talking about how to get the actual column causing the problem, however this would only work if this was a Script task.

This error is quite misleading! At first I thought it actually had something to do with the column length as stated but then i reduced the number of records I'm actually working with:

SELECT TOP 12610 * FROM FormSubmissions

The minute I go to 12611 the problems start. Then I started messing around with the DefaultBufferMaxRows and DefaultBuffer trying to use this article to get some inspiration.

My final buffer settings are as follows:

  1. DefaultBufferMaxRows = 500
  2. DefaultBufferSize = 7500000

Table of alternative buffer settings and their results
The table above shows some of the results where the number of files that the flow should process is 20438, the number successfully processed is 20172 and the number of error records that get written out to the flat file is 266. This changes according to the buffer settings and like I said in the beginning if I change my sample size to below 12611 everything works fine.

My final thought was that the record at position 12611 might have a column with data in that was the cause of the problem. So i changed the SQL statement responsible for extracting the data to include the following:

offset 12610 rows fetch next 1000 rows only;

Clicking the Preview correctly produces results to the query works. But this made no difference.

Additional errors that seem to pop up depending on the changes I make to try and address this issue:

  • System.Runtime.InteropServices.COMException (0xC0047020): Exception from HRESULT: 0xC0047020
  • [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Save all files to the temp files table" (131) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (138). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

I'm not exactly sure what to try next. Any help would be appreciated.

Jacques
  • 6,936
  • 8
  • 43
  • 102
  • A few quick approaches to attempt to find the cause. 1) Write the rows to a file and inspect that. Possibly even add a successor data flow that is Flat File -> ADO.NET destination. That can help identify whether it's unexpected data, and or configuration of the destination component. 2) Dial DefaultBufferSize down to 1. When the task fails, you'll know exactly which row is offensive and debug from there 3) Change the destination table to a new one and look at the suggested size and typing and compare to the actual destination. Run the data into the new table and see if it works. – billinkc Aug 11 '22 at 15:15
  • If 3) works, then make sure you don't have a trigger or something unexpected like that associated to the table that might be causing the weirdness. If 3 fails, post the ddl along with the offending row from attempt 1 and maybe we can get a reproduction going – billinkc Aug 11 '22 at 15:17

0 Answers0