March 16, 2016 at 10:17 am
Hi guys,
I am after some much need advise with how to insert data from one instance database into another instance database when the source instance name and database name are dynamic i.e. I have multiple SQL instances each with multiple databases, firstly I needed to iterate through the instances and locate all databases with a specific table. I have created this in SSIS using this great guides:
https://thesqldev.wordpress.com/2013/02/20/iterate-over-databases-using-ssis-part-1/
https://thesqldev.wordpress.com/2013/03/23/iterate-over-databases-using-ssis-part-2/
This gave me two variables that I can use to name the instance and the second for the database name.
I would now like to use these variables to connect to the source instance and database to execute a query to retrieve the data from each of the specific tables and insert it into a central database.
Any help appreciated.
Adam
March 16, 2016 at 10:27 am
Adam
You don't say exactly what you're struggling with. I haven't read through those links that you posted, but what I'd do is have two separate packages. The first package is the parent package that loops through the servers and calls the second package for each server. You can configure the server name in the connection manager in the second package from a variable, and in turn you can set the value of that variable from a variable in the parent package.
Hope that makes sense
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply