SSIS package problem - SQL Server 2008 R2 SP2 Standard 64 bit - Getting error "Unable to enlist in the transaction" even when package is not using transactions

• Monday, January 21, 2013 11:02 AM

   

   
  

  0

  Hello,

  I have problem with one SSIS package (ImportData) on production server. The package works most of the time but sometimes it fails with error:

  Executed as user: XXXXX. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  21:03:45 

  Error: 2013-01-20 21:04:00.23     Code: 0xC001402C     Source: XXXXXXX Connection manager "XXXXXXX"     Description: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".  End Error 

  Error: 2013-01-20 21:04:00.23     Code: 0xC0202009     Source: XXXXXXX Connection manager "XXXXXXX"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8004D00A.  COM error object information is available.  Source: "XXXXXXXX"  error code: 0x8004D00A  Description: "The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".  ".  End Error 

  Error: 2013-01-20 21:04:00.23     Code: 0xC00291EC     Source: XXXXXXXXX     Description: Failed to acquire connection "XXXXXXXXX". Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error 

  Error: 2013-01-20 21:04:00.24     Code: 0xC0010014     Source: Sequence Container      Description: One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.  End Error 

  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  21:03:45  Finished: 21:04:41  Elapsed:  56.235 seconds.  The package execution failed.  The step failed.

  SSIS Package arhitecture:

  • There is a master SSIS package (ImportData)  which sequential runs 3 other packages (2 packages transfers data from Oracle 11i to SQL server database, one package transfers data from file system - csv files to same SQL server database).
  • Packages which transfer data from Oracle are using Microsoft OLE DB provider for Oracle 32-bit
  • All SSIS packages and SSIS tasks inside packages have "TransactionOptions" set to "Supported" (default value)
  • MSDTC server is configured properly (as said here http://www.sqlservercentral.com/Forums/Topic723190-148-1.aspx)
  • All packages are stored/deployed on same Integration Services (SQL Server 10.50.4000) in MSDB folder as database
  • All packages are using stored procedures+write+read on same server on same database
  • I've created scheduled job for SQL Server Agent (same server) and correctly configured (permissions) proxy user account to run this job
  • Job is executed every day at 19:30 and 21:00
  • I have prod/test environment -> on test the SSIS package runs OK.

  Environment:

  • Windows Server 2008 R2 Standard SP1 64-bit
  • SQL Server 2008 R2 Standard SP2 64-bit
  • Virtualized servers

  Problem:

  • Most of the time SSIS package on production runs OK
  • Sometimes it fails with above described error
  • Sometimes it fails even if I manually runs SQL Agent job (no pattern)
  • The child package which transfer data from file system is the only one responsible for master package failure. Data transfers from Oracle are OK

  Thank you for your answers.

  
  

  • Edited by matejpi Monday, January 21, 2013 12:30 PM
  •  
  • Reply
  • Quote