SSIS dataflow cannot aquire connection even though it did earlier in the control flow

1

I have a package that connects to two different DBs, does some calculations, and then inserts values into each DB. The package worked great in our old env, but migrating it over to the new one has proven pretty unpleasant. We had to switch from Windows Auth to SQL Auth and the Connection Manager test works fine, but I'm getting the following error when attempting to execute my SSIS package from Visual Studio:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DB1" 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: OLE DB Source failed the pre-execute phase and returned error code 0xC020801C.

What has me really puzzled is the failing task isn't even the first time that connection is used in the package. Just before reaching that Dataflow task, an Execute SQL Task fires against the same connection and is successful. I can also go into the task itself and hit "Preview" and it returns data.

Does anyone have any ideas where else to look?

I've tried:

  • setting to 32bit Execution
  • deleting and recreating the task
  • delay validation = true
sql-server
ssis
asked on Stack Overflow Jul 8, 2020 by Moose

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0