SQL Server Integration Services. Dynamic changing of connection string by package parameters inside of foreach

2

I need to get data from several databases using SQL Server Integration Services (more than 20) and for each of them execute the package that transfers the data to a target database, there is a table with a list of connection strings of these databases in other database.

I try to execute the ExecutePackage task inside foreach task and send connection string from query as package's parameter, also I send connection string of target database and date. But package stops with error:

[Importing [11]] Error: SSIS error code: DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The call to the AcquireConnection method of the connection manager "targetdb" failed with error code 0xC0202009. You may have previously published error messages that contain more detailed information about the reason for the failure of the AcquireConnection method.

I think the connection manager of targetdb does not pass validation, in this case I run package directly from Visual Studio. When I run package separately (not inside foreach and with default parameters), then it works right.

Is it right way for my task?

sql-server
ssis
asked on Stack Overflow Apr 4, 2017 by justlogin • edited Apr 4, 2017 by marc_s

1 Answer

0

Decision was simple. I set DelayValidation property to true for package, which executed inside foreach loop, it decided problem.

answered on Stack Overflow Apr 27, 2017 by justlogin

User contributions licensed under CC BY-SA 3.0