July 9, 2021 at 1:22 pm
Hi Guys,
I have a situation where at two servers (Let's say A and B) I have a database db with the same structure at both of them. I need to create a process which would, every day, iterate through one of the schema (x) and move data from server A to server B.
So the task came down to creating the loop through tables in db and for each of them perform the below operation:
insert into B.db.x.tb
select *
from A.db.x.tb
In my case I cannot just create an linked server and use simple stored procedure with a loop. So I am trying to use SSIS.
I tried create an foreach loop with ADO.NET schema Rowset Enumerator. Which would save table names to variable. Unfortunately inside of 'OLE DB Source' I have an error that SSIS cannot retrieve column information...
Could you please advise if that is even possible? At this moment I am mapping tables one by one... but it does not seem to make a lot o sense... (there is quite a lot of them, any change in the logic will require lots of changes, schema change will require package change...).
July 9, 2021 at 1:27 pm
Are you truncating the target tables first? If not, how are you avoiding duplicates?
Might be simpler to perform a backup/ restore - have you considered that?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 9, 2021 at 1:32 pm
Hi Phil, I am deleting data from a source after that, so I do not expect any duplicates. The goal is to gather metadata from a few servers in the the one place so backups/restores does not seem to be an option...
July 9, 2021 at 2:30 pm
Metadata, rather than actual data? What sort of metadata?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 9, 2021 at 2:56 pm
Is it make any difference? These are just statistics about servers collected in diagnostic peruses. For example drive space utilization. Currently I collect that at each server separately. I would like to store that outside of the box, at in one place.
July 11, 2021 at 9:11 am
Is it make any difference? These are just statistics about servers collected in diagnostic peruses. For example drive space utilization. Currently I collect that at each server separately. I would like to store that outside of the box, at in one place.
Going back to your original post, having SSIS dynamically change its column mappings at run time is not possible.
But retrieving data from multiple instances with the same table formats (by looping) is possible.
So if you have (say) five different types of information which you are collecting, in five different tables, and those five tables exist in all of the instances you want to collect information from, you could set up five FOREACH loops, each containing a dataflow for that particular table format.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply