SSIS-Catastrophic Error (On Large data)

• Friday, August 31, 2012 9:23 AM

   

   
  

  0

  I have a winform( C# ) from where I am calling this DTS package using the APIs provided in Microsoft.SqlServer.Dts.Runtime.dll;

  Threading is not implemented. Currently the winform is just executing the DTS package via APIs.

  In the DTS package, following are the activities performed - 

  1. Read a XML file and get the list of items (Energy Meters) and store in a DTS variable object

  2. In a For-Loop Container, for each item in the object, data transfer activities are done from a source table to destination table

  - RetainSameConnection is set to true for the connection string

  - Windows Authentication (with Admin Rights) is being used

  - "SQL server Destination" component is being used because we need to fire triggers that are not possible using Oledb Destination

  Error Reproducibility-

  When the DTS is executed for 1000 items it runs successfully for 85-90% items but it fails towards the end. The error has nothing to do with the selection of items in the XML. Even if I configure one item and repeat it for 1000 times, initially it runs successfully but fails when it reaches towards the end of the list.

  Important: If I run the DTS in the *SQl Server BI Development Studio" it runs successfully throught the package. But if the same DTS package with the same list of items in XML is executed using the APIs in DotNet, it fails with catastrophic error.

  Error
  

  -1071636421: The thread for the SSIS Bulk Insert task failed initialization.

  -1073450982: component "SQL Server Destination" (276) failed the pre-execute phase and returned error code 0xC020203B.

  -1071636417: The thread for the SSIS Bulk Insert task terminated with errors or warnings.

  -2147418113: Catastrophic failure
  

  
  
  

  • Edited by J.Edgar Friday, August 31, 2012 9:23 AM
  • Edited by J.Edgar Friday, August 31, 2012 9:29 AM
  •  
  • Reply
  • Quote