The acquire connection method call failed error

Sometimes the actual error lies somewhere and the error message directs us to look at something else. We end up wasting time in this process as the error message diverted us. This is kind of error in this category.

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

If DelayValidation is not set to true, SSIS engine uses the design time values of a task until it actually runs a task. Since we are dynamically setting the connection manager, instead of using the dynamically set value (Which it uses anayway while running the task), it tried to validate the task with the design time values set for the connection manager. Unfortunately, the service account with which the job is running doesn't have access to the server and database set during the design time for the connection manager. So, the validation of the task failed causing the job to fail.

Set the "DelayValidation" property to true in your package. It will work fine.