I am having an issue when running an SSIS package directly on a SQL server. The package moves data from tables on a SQL server 2005 inside our network out into a sql server 2005 in our DMZ. Ultimately I'd like to run this from a scheduled job on the server....
The error that I am receiving is as follows:
Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ABC" failed with error code 0xC0202009.
I originally thought that this may be due to the credentials required for one of the connections not being passed correctly. For this reason, I changed the encryption level of the SSIS package to be EncryptAllWithPassword. I then ran the package in BIDS successfully. I then connected to the server through SSMS on my machine (using my own windows account), and imported the package into the MSDB, supplying the password. I then ran the package on my machine through SSMS successfully. However when I opened a remote desktop session to the server itself (again, using my windows account) and ran the package through SSMS, I received the error above. A colleauge has also run the package successfully through SSMS on his machine, but also received the same error when opening an RD session ot the server itself.
So I believe that the password information associated with each connection must be stored correctly, otherwise how would my colleague have been able to run the package? This leads me to think that ProtectionLevel must be working - most web searches however for error 0xC0202009 suggest that ProtectionLevel could be the cause, or the package creator (our domain SQL account) could be different to the account running the package. Yet I have run the package successfully and my account is different to the package creator, and our DBAs cannot run the package when logged on to the server as the package creator.
I have also tried importing the package onto a different SQL server, but exactly the same error occurs.
Any ideas on how to resolve this?