SSIS cannot acquire connection manager when executing a task but connects successfully in manager and shows preview data

1
votes

I get below error message when I try to execute the datflow task in SSIS. the connection manager connects successfully when I test the conn. I can also preview data from the OLE DB source. I get error when I try to execute though,

[[1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[SSIS.Pipeline] Error: component "" (1) failed validation and returned error code 0xC020801C.

I tried the below change and nothing worked:

  • Set False to Run64BitRunTime
  • Tried Delay Validation with both true and false
  • Set Transaction Option not supported

Are there any other options which I should modify ? SSIS 2008 version is being used.The data flow tasks connects to a Oralce DB. the connection manager uses Oracle Provider fro OLE DB

3
sql-serveroraclessisolessis-2008
I made use of config file and the data source credentials in it for the data flow task. Somehow, it started working – TheDamned

3 Answers

1
votes

I too ran into the same problem. I was using a config file and the package was reading Connection String form there.

To solve this. Open config file in notepad and type in the password Manually. Save it and run the package.

1
votes

So this happens when you are creating a package configuration file. If you have selected the connection string while setting it up, it tries to fetch the connection string from the configuration file. So, open the configuration file and enter the password after the user id for the connection string value in this manner

User ID=[your user ID here];Password=[your password here];

And it will work

0
votes

Sometimes visual studio behaves weird, just try deleting the task in SSIS package and add again.