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
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.
- Windows Server 2008 R2 Standard SP1 64-bit
- SQL Server 2008 R2 Standard SP2 64-bit
- Virtualized servers
- 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
Tuesday, January 22, 2013 4:37 PMModerator
I suggest you visit thread http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/0cbb8ca0-0633-4edf-ae8a-956e3864c079
where the answer proposed to apply the latest SP, but there is chance the connection cannot join a transaction simply because of the race condition.
The cure there is not to use tasks running in parallel.
Arthur My Blog
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Monday, January 28, 2013 5:03 AM