The deadline required X number of SSIS packages to be done in each day, a deadline which was tight but doable, as long as there were no technical surprises. The packages were written in SSDT for SQL Server 2012, and most of them were 
connecting to other SQL Server 2012 databases with no problem. The next package and contained an Ole DB connection to a SQL Server 2008R2 database with a SQL Server defined ID and password. I made the connection, which tested successfully, opened up the data task and was able to preview the data. Everything looked fine. I right clicked on the package to execute it, and it went along fine until it reached the data task to get data from SQL Server 2008R2. Surprise! The dreaded red X appeared. Failure.
AcquireConnection method call to the connection manager failed with error code 0xC0202009/0xC020801C
Try as I might, the only thing I was able to do after an hour was periodically change the error code from 0xC0202009 to 0xC020801C. Nothing I did worked. I created a new connection, created a new OleDB Source, changed the Run64BitRuntime to False in Configuration Properties in the Debugging Section of the project execution, set the Data Flow task DelayValidation from False to True. None of these various suggestions that I got from various websites worked at all. I thought about changing the SSIS Service ID’s execution properties, but since I was running in Debug mode I determined that this would not make any difference, so I abandoned that idea. Nothing worked. The only thing I was able to do was change the error code, not eliminate it. I could log into SQL Server with the same ID and password in my package and run the simple query in the data flow task and return data. I could preview the data, what I couldn’t do is execute the SSIS package. Out of desperation I rebooted, which also did nothing.
EncryptSensitiveWithUserKey fixes error code 0xC0202009/0xC020801C
While I was thinking about what was left to try, I was wondering why I was getting a failure to connect message and was thinking about passwords. Because the project and of course the package were set to DontSaveSensitive, every time I 
opened the connection, I had to enter the password again. News flash. DontSaveSensitive means what it says. It doesn’t save the password, so when you run the package, you have no password and therefore cannot acquire a connection. The fix was to change the package to EncryptSensitiveWithUserKey. Once I did that, the magical green check appeared.
Hopefully if you ever get this message you will find this post quickly and not waste the time I did figuring this out.
Yours Always,
Ginger Grant
Data aficionado et SQL Raconteur
Pingback: AcquireConnection Failures – Curated SQL
I can relate to your pain, but am glad you got it solved. I hope your post helps others. Eek.
One other troubleshooting step that I’ve had work in the past after many hours of troubleshooting, is moving a connection from a “project” connection to a “package” one, particularly if it’s a 3rd party connector. Not saying it would have helped in your situation at all (it wouldn’t have), but just to give readers of your blog something else to try if they’re still stuck after reading your post. I almost always use project connection managers, so this has gotten me a couple of times in the past.
I met Matt Masson at a PreCon recently and talked with him about this problem. I had to set the password as a sensitive property in Project.params. Its Matt’s opinion this should work and is a bug, he forwarded this to the SSIS team.
thank you. You just saved me (more) hours of fruitless debugging
Ginger Grant, Thanks a lot man ! You saved the day !!!! I was struggling with this issue for like two days, tried everything that was possible and not, and today I’ve ran into your post. The solution did the trick and now I an run packages from my VS with connections to Azure SQL server. !! You did great ! Thanks again !
Thanks a LOT !!! You’re the best!
You are very welcome Andrej!