You can launch a Data Transformation Services (DTS) session that moves data between two computers from a third computer, but understanding where DTS runs can be difficult. The most important thing to remember is that SQL Server stores DTS packages by default in the sysdtspackages table of Microsoft database (msdb). SQL Server stores DTS packages as binary large object (BLOB) data.
You can also save DTS packages in the Microsoft Repository or as a DTS file. DTS files are COM-structured storage files that can save multiple packages and versions. (Packages saved to SQL Server or the Repository can also save multiple versions of a package, but not multiple packages under the same package name. If you want to save multiple packages to the same DTS file, simply use different package names and the same file name when you save them.)
What's confusing is that the DTS packages run on the client in almost the same way an executable file does. The trick is figuring out which machine is the client. The following scenarios will help you understand where DTS runs.
Scenario One
You have three machines called Client, Source, and Target. From Client, the user runs a DTS package stored on Target that moves data from Source to Target. The user executes the DTS package by opening the DTS Package Designer from Client and clicking the Execute button on the package.
DTS packages are free-threaded COM objects and run almost like a program. In this case, the DTS package acts as if it were an executable file stored on Target, which acts more like a file server than a database server. The DTS package runs on Client, pulls data from Source, and moves it to Target. The package moves data over the network twice, and the Client CPU does most of the work.
Scenario Two
In this scenario, you still have three machines called Client, Source, and Target, but this time you use a different technique to execute the package. The package is still stored on Target, but you schedule the package as a Job by using SQL Agent on Target. This time the Client user simply executes the SQL Agent job that runs the package. In this case, the package runs in the context of the user account that runs SQL Agent on Target, and the package pulls data from Source to Target by using Target’s CPU processing power.
