Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joe.reddy7
Starting Member
1 Post |
 Posted - 2010-02-24 : 21:48:13
|
| The requirement is to load a table (in server A, database A) with a query which involves selecting data from three different servers, three different databases and 3 different tables. The query is very simple as select, from, where, group by and order by. They are asking me to design an SSIS package for this. Daily data gets loaded. What I did is in my package, I added an execute sql task and run it as a job. Though this package gets the job done, I know it wouldn't be that simple. Any idea of overall ssis package would be really helpful. |
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2010-02-26 : 06:35:21
|
| Load ServerA.DatabaseA.TableA from ServerB.DatabaseB.TableB, ServerC.DatabaseC.TableC, ServerD.DatabaseD.TableD - This is what you are looking at RIGHT. I hope three tables that you want to load have same structure as TableA. Option 1) Write a stored procedure which does the collection of data from different servers and use SP in execute SQL task. Dont forget to enable ISQUERYSTOREDPROCEDURE to true. There are some caveats to this process like you have to set-up linked server on source where you want to create this SP which is server A in this case. Option2) use variables to make dynamic connection. |
 |
|
|
|
|
|
ssis package, help needed