August 1, 2008 at 12:58 pm
I want a ssis package as per following criteria
1. I have various source sql server ( say server1, server2,server3,server4)
2. on each of these servers I have a same db called - TestDB
3. I have only one destination server ( say destination1)
Now I want to develop a SSIS package which will connect to each server as per source list and export data in destination db. ( 2-3 tables need to export)
In future I may add source server list ( say server5,server6) , in this package i will put only new source list and same package should execute.
is it possible ?
thanks
bang
August 1, 2008 at 1:04 pm
you can do that with a single SSIS package....but it can't load the data from more than 1 server to the other server at a single time...what you have to do is, create an ssis package and configuration file...so by changing the server names in the configuration files, you can transfer the data from different sources to diff destinations....
August 1, 2008 at 1:55 pm
but chaging name can happen dynamically like it will call first server1 then transfer data to destination , then it will call server2 then transfer data and like..on...
August 1, 2008 at 2:02 pm
it dynamically takes the server names from the configuration file...so u need to change the configuration file regularly to shift the servers....may be there should be any other way to achieve this...
August 1, 2008 at 2:19 pm
Just guessing..
u can use for each loop,
provide the container with server name and location thru a variable...
and then, ETL can be performed.
AS i see here, the tricky part is talking dynamically with three servers.
so, create a variable which contains server name.
August 1, 2008 at 2:29 pm
I would use a "For Loop Container" with a Script Task as the first step in that container. On the first pass, the script task would retrieve the list of servers to use as the source. With each pass thru the loop the script task would set the source of the data connection to the first server that has not been queried yet. When script task gets to the last server it would also change the value of the variable used as the EvalExpression so that the looping would end.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 1, 2008 at 7:21 pm
Hi ,
Can you tell me how to do this?
or tell me steps one by one ..may be screenshot would be better..
thanks
bang
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply