August 11, 2005 at 6:00 pm
Hi All,
I am in the process of re-building a data warehouse, and I have a question about where the work is REALLY done in a DTS package (Source or Destination).
For instance, I am using DTS to extract data from Table1 on SQLServerA to my build table on SQLServerB. To get all the data transformed, I have to eventually join Table1 16 times with itself (don't ask). The existing process has souce and destination databases on the same server. It also has the 16 joins happening during the extract, which seems to me like too much work for the extract phase.
If I create and execute a DTS package on SQLServerB to pull the data from SQLServerA, and if I have the 16 joins in the select statement in the DTS package... will the work from the joins occur on SQLServerA or on SQLServerB???
I am not that familiar with DTS, so I am not sure if ServerB keeps hitting ServerA for each join, or if ServerA does all the work and hands it to ServerB. I think it's the latter... but I am new at this DTS game.
Hope that makes sense. Thanks in advance
-Rob
August 12, 2005 at 3:05 am
DTS itself runs on the CLIENT machine - so e.g. if from your PC you open up SQL Enterprise manager and run one of the DTS jobs on any server that job runs on your machine - however this of course applies just to the DTS steps (transforms etc) of the job - as SQL Server is a client/server database the DTS job is like any other client to the SQL server so the SQL jobs run at the server - your question seems to relate to two linked servers - if you have a SQL staement for you 16 self joins that only references the server that has that table then all the work will bedone by that SQL Server - however if you have linked servers and do something like SELECT ... from servera.db.owner.table on serverb then most of that work will be on servera - if however the join includes tables on servera and serverb it gets much more complicated and you begin to hit distributed transactions etc.
James Horsley
Workflow Consulting Limited
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply