For several days now I am trying to make a connection to the BDD through the OLE DB wizard in Sql Server Integration Services. What I need is to be able to connect to different servers to be able to do the same query but in all the servers at the same time. In a table, I have parameters that identify each server.
How can I access a connection on all servers to perform the same query?
That is, I have 40 different databases that contain information from 40 different branches, what I do not want is to generate 40 OLE DB origins and I would like to know if there is any way to make an origin variable and through that source you can consult the 40 BDD with the same SQL query.
What I'm doing at the moment is adding a foreach and the foreach set it up like this:
The connection files have the following structure:
Data Source = 136.4.3.7; Initial Catalog = MundoEApp; Provider = SQLNCLI11.1; Integrated Security = SSPI; Auto Translate = False;
In the part of the foreach variables I have the following:
The part of the startup connection is configured to a server using Windows authentication and then modify the ConnectionString property as follows:
When I try to execute the SQL query, it sends me the following error:
I appreciate your help in advance, regards.
