November 29, 2011 at 5:02 pm
I've had running for a few years a simple DTS package that inserts rows from SQL Server into a AS400 staging table. The destination table has no indexes. SQL 2008 R2 has forced me to rewrite this as a SSIS package. The new SSIS package more or less (heavy on the less) works, but the problem is only 341 of the 1105 rows are inserting for my test case.
For the SQL Server source I'm using OLE DB. For the AS400 destination I'm using ADO.NET for ODBC. There is no data conversion, my SQL table is a staging table that matches as close as possible to its counterpart staging table in the AS400.
I have Logging turned on and also the 764 rows that fail are being redirected to a text file. There is no indication of error in the Logging file and for the life of me I cannot see any difference between the data that fails versus the data that works. Again DTS has no problems inserting all rows.
It is worth mentioning that I created the SQL side staging table because I thought SSIS is more strict about data types. But running the original SQL Select statement versus using the table to table insert both results in only 341 inserts.
My question: is there more I can do in SSIS to detect what the problem is? Nothing I have done is telling me the "why" for the failure. I appreciate any ideas, this problem has frustrated me to no end! Thanks!
December 2, 2011 at 9:45 am
I'll answer my own post, in part. The flat file that contains the redirected failed rows has a couple of error columns. One of the columns is the error code number, in my case -1071610801. Don't search for the error code with the dash, in fact Google won't return any results. On the Microsoft's web page for the SSIS error codes (http://msdn.microsoft.com/en-us/library/ms345164.aspx) it shows the dash in a table but it is offset from the number, which explains the zero search results
The description for my error code is non-helpful: An error has occurred while sending this row to destination data source. A Google search for "1071610801 SSIS" only returns about 120 results none of which are helpful. On to the Microsoft forums for this one.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply