Hi there, I've a package that extract data from Informix and loads into SQL Server table. I'm using SQL Server 2008 R2. Package is using ADO.NET source to connect to Informix and ADO.NET destination to load the data in SQL Server table. I'm redirecting error rows to exception table in sql server using OLEDB destination. When the records are redirected the exception table the package is configured to fail. I'm also capturing the error description using script task and inserting it into the exception tables. I've noticed that the package fails randomly. But when I try to re-run the package it works fine !! Also if i try to insert error records from exception table into destination table it inserts successfully. Table structure for destination and excpetion tables are identical. The only difference i can see is the different providers for the insert. Also there are 15-20 different packages that are run on nightly basis and it's only this package that fails randomly. Has anyone come across this issue before? Thanks in Advance. Regards, Vivek

Hello Vivek, Would you be able to capture and post here the exact error message? If not done yet, I suggest you implement good logging.Arthur My Blog

Hi Arthur The error message that i captured using the script component is very generice. It's "The operation completed successfully. An error has occurred while sending this row to destination data source.". This is the error message I'm inserting into exception table for investigation. As I've configured the package to fail if the count of records in exception table > 0, the error message I see in the sysssislog table is not helpful. The error message in sysssislog table has the datacode value of -1073548784. Currently I've "OnError", "OnTaskFailed" and "BufferSizeTuning" events selected for logging.Regards, Vivek

I would start thinking about redesigning your package, namely the way you capture and log error rows. Can you share in details how you implemented that? And if you remove this functionality will the package always succeed? Arthur My Blog

Basically in the dataflow this is how my data flows through. ADO NET Source >> Source Row Count into @Source Variable >> Derived columns >> Insert Row count into @ Insert variable >> ADO Net Destination >> ADO NET Destination Error Output >> Error Row Count into @Error variable >> Script Component (to get error details) >> OLE DB Destination. (Exception table) Since the error records are redirected to exception table the package doesn't fail. I've a error table which gets updated if my @Error > 0 or @Source <> @Insert. Regards, Vivek