One of our SSIS packages in the catalogs errors out/fails intermittently when connecting to an oracle database that is on a different server. The package is scheduled to run every 10 mins, 24 hrs a day, and 7 days a week. The package fails about 40% of the time every day. The error messages from the catalog for every instance of the package failing are:
1: WXYZ Database failed validation and returned error code 0xC020801C. 2: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. 3: Error: SSIS Error code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.The AcquireConnection method call to the connection manager "WXYZ" failed with error code 0xC0014009.
I understand that this is a common error, but we have not been getting any errors since the package started execution almost a year ago. The thing is it only errors out 40% of the time. What could be the possible root cause for this error? I read online that it could be compatibility issues between 64-bit and 32-bit databases, but would not it error out all the time if that is the case? Thanks for any help.