SSIS sometimes inserts fewer records with the same source data set

Question

I have a table with 63,506 records. After running through a fairly complicated dataflow, the number shown flowing to the SQL Server Destination always matches my initial record count, yet SOMETIMES, not all records are inserted into my destination table. The flow always completes 'successfully', but it's only truly successful maybe half of the time. The data source is definitely not changing, so i don't know what could be causing the inconsistencies. Any help on how to troubleshoot this would be greatly appreciated.

Thanks, Jason

Answer 1

I've seen this happen several times with the SQL Server Destination and a few times with the OLE DB destination. This generally happened when I was committing a large number of rows (>1 billion rows) to a table. To audit the data to make certain everything is working correctly, I added a RowCount data flow transformation to record the imported records and executed a stored procedure after the data flow the compared the actual number of records inserted to the variable. This happened to me in the SQL Server 2008 CTP and RTM, but I haven't had this happen in R2 yet.

I would recommend ditching the SQL Server Destination for the OLE DB destination. Your performance will suffer maybe by 50%, but you will be able to debug the package from your own machine without being logged into the SQL Server and avoid this error. My suspicion is that something is going wrong with memory limits and the SQL Server Destination seems to be more memory intensive than the OLE DB destination.