I have the scenario below:
97 SQLEXPRESS instances from 97 different servers. Each instance has a database with the same name and schema. I need to retrieve one table from all 97 servers and export it to a separate SQL Server 2019 server to control and generate reports.
Edited: The need for SQL EXPRESS on 97 servers is because those servers are installed on 97 OIL platforms in the middle of the sea. To avoid connectivity issues, each platform has its own environment.
I've created an SSIS project in Visual Studio 2022 to handle this data export, which I can deploy on an SSIS server to run daily. My package includes a foreach loop with an expression variable for the origin connection to dynamically change servers.
In the Control Flow, I've set up an Execute SQL Task to retrieve my server table from the SQL Server using this query:
SELECT table, instance, base, codserver FROM dbo.Server WHERE active = 1
Then, I load the results into the User::Tablelist variable of type Object.
In my Foreach loop, I use an "Enumerator ADO foreach" with the User::TableList as the "ADO source Variable". Then, in the "Variable Mapping" section, I map User::table, User::instance, User::base, and User::codserver.
In the Data Flow, I create an OLE DB Origin and use an expression to dynamically set the Initial Catalog and ServerName from my variables.
I add a new column to include the codserver column, which indicates the server from which the information is being retrieved. This column will be used for filtering after the export.
Then i add the Ole DB Destination.
Edited: My destination server is a licensed SQL Server 2019 Standard with licensed SSIS and Report Server as well.
This setup works in development. However, I encounter issues when deploying it to my SSIS server because I can't export the variables I created in the Visual Studio project.
I attempted to use package parameters, but I encountered difficulties accessing the column from the server table and incorporating it into my parameters to configure my connections.
I'm exploring alternative solutions using XML, JSON, or any other method. If I can't find a better alternative, I may resort to creating a package to generate a separate connection for each of the 97 servers, although this seems inefficient.
Can someone assist me in finding a more efficient way to accomplish this?
My primary concern is finding a solution that can be deployed once for a daily SSIS job for all 97 servers.