The AcquireConnection method call to the connection manager "ConnectionManagerName" failed with error code 0xC202009

Question

Hello,

I have an SSIS package (2016, 64 BIT) that runs nightly through SQL Server Agent: both the source and destination servers are 64 bit and SQL Server 2016. The package loads incremental data from source to destination. The package runs fine most of the time except occasionally it fails with the following error message "The AcquireConnection method call to the connection manager "ConnectionManagerName" failed with error code 0xC202009". There are two connection managers (source and destination). The error message fails on both connection managers interchangeably. When I restart the job, it runs successfully every time. So, if it was related to login issues, the job would have failed when I restart it. I googled the error message and the below link seems to address the issue but it is relevant to "Microsoft JET Database Engine": the-acquireconnection-method-call-failed-with-error-code-0xc0202009
Here are screenshots.

Any suggestion or solution is really appreciated.

Thank you very much in advance!

Answer 1

Hi @In-efficient ,

1.Please check if you set the connection string correctly.
2.Please check if you set the user or password correctly in the connection manager.

The following links will be helpful:
SSIS connection manager login fails
SSIS package hits “login failed for user” when simultaneous operations occur against the same target database
SSIS package does not run when called from a SQL Server Agent job step

Best Regards,
Mona

---

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.

Answer 2

Hi Mona - Thanks a lot for the reply.
I checked both your suggestions and both are set correctly. The package scheduled in SQL Server Agent and runs successfully. It only fails once/twice a month except it failed multiple times last week. So, if there was an issue with connection string or password setting in connection manager, it would not have run to begin with. I am kind of confused between what the error says and there's no login issue in the package.

Thanks again!
~In-efficient

Answer 3

@Mona - The DelayValidation is set to False for the Connection Managers. And the ProtectionLevel is set to EncryptSensitiveWithPassword. This package is modified by multiple users. If I need to change it to EncryptAllWithPassword **, what impact will it have on the package?
If the Connection Managers are set **DelayValidation to False the package is validated at the package level and at the components level. If the setting is changed from False to True does that mean the package gets validated at the components level during the package runtime only? How does changing DelayValidation from False to True will help?

Best,
~In-efficient

Answer 4

Hi @In-efficient ,

1.Use of Delay validation
We set the delay validation properties is true when we are creating the dynamic connections. Some time we need to run a bit of code in a container, or task that creates an object. If the package tries to validate it may fail because the object does not exist. By setting this properties as true the take validate the connection at run time.

2.And the ProtectionLevel is set to EncryptSensitiveWithPassword. This package is modified by multiple users. If I need to change it to EncryptAllWithPassword , what impact will it have on the package?
a.Encrypt sensitive with password (EncryptSensitiveWithPassword)
Uses a password to encrypt only the values of sensitive properties in the package. DPAPI is used for this encryption. Sensitive data is saved as a part of the package, but that data is encrypted by using a password that the current user supplies when the package is created or exported. To open the package in SSIS Designer, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data.
b.Encrypt all with password (EncryptAllWithPassword)
Uses a password to encrypt the whole package. The package is encrypted by using a password that the user supplies when the package is created or exported. To open the package in SSIS Designer or run the package by using the dtexec command prompt utility, the user must provide the package password. Without the password the user cannot access or run the package.

Please refer to Delay validation properties in SSIS and Access Control for Sensitive Data in Packages.

Best Regards,
Mona

---

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.

Answer 5

Hi @Mona - Thanks for the explanation. So, what do you think I can try as a solution?

Thanks
~In-efficient