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.
