SSIS Error: The AcquireConnection method call to the connection manager Source failed with error code 0xC0014009.

Question

Hello,

We have a Job in MS SQL Server Management Studio. This job runs several Visual Studio SSIS packages. When we launch the job it fails at step one.
Step one is to retrieve data from a AS400 DB and place it in some ODS tables within our DWH.
When we run the same package that fails from Visual Studio, there is no issue. It runs perfectly. It only fails when ran from SQL Management Studio,

One of the errors I get is:

Code: 0xC0014009 Description: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. Code: 0x0000020F Description: The AcquireConnection method call to the connection manager Source failed with error code 0xC0014009.

----------

Environment Description:

System OS: Windows Server 2019
SQL Client: SQL Server Management Studio 15.0.18384.0 (MS SQL Server Tools 18)
Visual Studio 2019

I have reviewed several articles in various blogs/platforms.

I have checked the following:

• We have System DSN connections defined for AS400 DB both 32-bit and also 64-bit. Both have the same name assigned. The driver used is "iSeries Access ODBC Driver"
• MS SQL Server Management Studio tool is 32-bit and Visual Studio 2019 tool is also 32-bit.
• The user account that is used to run the package under SQL Server Agent is the same as the package author.
• The package protection level is EncryptSensitiveWithPassword
• Tried to log into the Database both with Windows Authentication and also by introducing user credentials. In both cases the user used has permissions to access the database.
• We force the 32-bit execution type from the Visual Studio package:
  
• We also tried defining a connection file in Visual Studio and deployed the project again. SQL job still fails with the same error code.
• We tried delaying the validation for the package that seems to be failing. We deployed the project again. SQL job still fails with the same error code.
• We also tried to open MS SQL Server Management Studio as Administrator and we still get the error.
• We checked the permissions on disk for the files used within the Visual Studio Project. The user that runs the SQL job has full control over the files. This should not be an issue.

Any idea on how to solve the issue? Is there any known workaround that could be applied to this situation?

I hope I have not left any details within my description.

Thank you very much for all the possible help.
Kind regards,
Romeo.

Answer 1

Hello,

I have been doing more tests and I managed to to fix the issue. Actually the cause of the issue was pretty silly.
It seems that the client owns two servers:

• Server 1: SQL Management Studio + VIsual Studio
• Server 2; SQL Server + SQL Management Studio

Most of the tests were performed on Server 1.

This being said, the actual issue was that on Server 1 we have all de ODBC connections configured correctly and this is why tests from Visual Studio worked correctly.
But when performing tests from SQL Management Studio, we were actually connecting to the SQL Server ( Server 2 ). On this server there were no ODBC connections defined.
So, I created the required connections in the System DSN tab in Server 2 and now the SQL jobs run correctly from both Server 1 and Server 2.

Thank you very much lending me a hand with my issue.

Kind regards,
Romeo.

Answer 2

Hi @RomeoIsfan-4660,

Please try to edit the existing SQL Agent job to change job step to run in 32bit runtime .

Under the Configuration tab --Advanced tab. Check/Uncheck the 32-bit runtime.

If this doesn't work, you may refer this to troubleshoot SSIS packages failed execution in a SQL Agent job.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 3

Hello @ZoeHui-MSFT ,

Thank you very much for taking your time in answering to my post. :)
This was something I missed to mention...
All the packages within the job are already marked as "32-bit runtime".

I have also tried to run the job at a step different than step 1, just to make sure that I was not having an issue just with the package in step 1.
I get the same errors.

One other thing I did not post was the image where we can see that from Visual Studio the package runs correctly.

Thank you very much once again!
Cheers,
Romeo.

Answer 4

I am having the same issue trying to connect to a snowflake odbc 32/64. What was the solution here? It works in visual studio but not when create a job and call the ssis package.