SSIS: Working package fails after upgrade to SQL Server 2012

Question

I have a SSIS package that was running fine on SQL2008R2 using Environmental Variables pointing to a configuration file for connection strings. Using VS2010 Shell, I upgraded the package for our SQL Server 2012 database. The package is still in package deployment model. The ProtectionLevel of the package is set to DontSaveSensitive. Run64BitRuntime = False. When I try and execute the package in VS, I am getting the following errors:

[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DB_Connection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.**

[SSIS.Pipeline] Error: OLE DB Destination failed the pre-execute phase and returned error code 0xC020801C.**

With full logging on, I see the first failure here:

Diagnostic,[ComputerName],OFFICE\username,DB_Connection,{5AD75239-D546-4AAF-963E-E195FC2F0C1E},{9EC48106-DDBD-40E9-8FBB-942BCF025EEE},3/26/2013 10:35:21 AM,3/26/2013 10:35:21 AM,0,(null),ExternalRequest_post: 'ITransactionJoin::JoinTransaction failed'. The external request has completed.**

The part that is really throwing me is that earlier in the package, the same "DB_Connection" manager is used successfully in a number of Execute SQL Tasks. So, it seems the connection strings are being read from the configurations correctly.

I have tried deleting\recreating the Connection Manager. I have verified that DTC is configured properly on both my local and the server. I have other packages connecting to the same SQL2012 DB using the same configuration method using Data Flow Tasks without a problem.

Any help anyone can give to point me in the right direction will be much appreciated. I would prefer to not have to move to a project deployment model at this time if I do not have to.

Answer 1

I was able to get my package to run locally. While I had checked the TransactionOption on my tasks to make sure they were set to Supported, it turned out that one of my sequence containers was set to Required. Not sure why this worked in SQL2008R2, but not in SQL2012. I changed my container to Supported and the package runs now.