Unanswered: SSIS Package Fails When TransactionOption Set to Required
I have an SSIS package with a squence container. There are 25 other containers within the sequence container. There are three containers outside of the sequence container. I have the Package TransactionOption set to 'Required'. I have the TransactionOption of the three containers outside of the sequence container set to 'Not Supported'.
The sequence container and all 25 containers within are set to 'Supported'. When I run the package, the package fails on the first container (a dataflow) within the sequence container. The error message indicates that the the OLE DB source in the Dataflow cannot acquire a connection from the connection manager. The connection manager is an ole db connection to SQL Server.
Do I need to delay validation on the Dataflow task, or is there something wrong with the isolation level?
--The problem occurs when I run the package in BIDS on a Windows XP machine.
--The database resides on a different server. The database runs on SQL Server 2005 Enterprise Edition 64 bit sp2.
--All containers within the sequence container pass validation. The first Dataflow container in the sequence container is called 'County2 Staging'. The error comes from the OLE DB source connection:
[County2 Staging Source ] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER . The AcquireConnection method call to the connection manager "CA_AddressUpdate" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[DTS.Pipeline] Error: component "County2 Staging Source" (1) failed the pre-execute phase and returned error code 0xC020801C.
The CA_AddressUpdate connection manager uses the OLE DB provider for SQL Server.
--At the package level, I have this error: [Connection manager "CA_AddressUpdate"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.". [Connection manager "CA_AddressUpdate"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D00A.
--On my Windows XP machine, I have gone to 'Component Services', and have configured the 'Security Configuration' of MSDTC as follows: Network DTC Access (checked), Allow Remote Clients (checked), Allow Remote Administration (checked), Allow Inbound (checked with 'No Authentication Required'), and 'Account' is set to NT AUTHORITY\NetworkService.
--In the properties of the package, I have tried to run the package with 'Run64BitRuntime' set to False.
Am I having this issue because my connection manager is an OLE DB connection manager configured for a database that is on SQL Server 64 bit? Could there be a firewall issue with the MSDTC?