Max_431
Max_431

Reputation: 23

SSIS Package failing- CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

I have this SSIS package with many tasks. While the package sometimes run fine but sometimes it just fails. I have observed one thing that the package fails because of couple of tasks, prominently one, and throw error as mentioned in the subject and few others along with it likes like " failed validation and returned error code 0xco20801c, login timeout expired, network-related or instance specific error, locating server/instance. Its able to acquire connection for other task just fine. i have like bunch of tasks but while running these few tasks it gives those errors. Few things i have checked and modified is ensure to enforce 32 bit and user running the job has valid credentials in configuration and yes i have not yet tried delay validation but as far as I have read it does not have impact especially since my package runs with 50% success rate. Note: I have recently migrated from 2012 to 2022 and yes the driver has changed in the new packages. Its Microsoft OLE DB for SQL Server whereas in 2012 it was Native client 11.0.And yes if i run it directly through visual studio it runs fine, no issues with connections. I have also discussed with network team whether they have drops in the network but they say its all fine. Please suggest what else i have to check or modify? Help is much appreciated since its been almost couple of weeks of this issues

Few things i have checked and modified is ensure to enforce 32 bit and user running the job has valid credentials in configuration and yes i have not yet tried delay validation but as far as I have read few articles it does not have impact especially since my package runs with 50% success rate. Also, in the connection manager of job retainsameconnection is false, i set it to true but that did not help. One more thing, connection string theres some parameter called application name in sql 2022 where as sql 2012 there was no such parameters

Upvotes: 0

Views: 28

Answers (0)

Related Questions