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.
Data aficionado et SQL Raconteur