Problem solve Get help with specific problems with your technologies, process and projects.

Don't overlook system context of DTS packages

With data transformation service packages, you can access and change data easily, but you must not overlook the context of the system in which you're running those packages. The machine running a DTS package may hinder its execution.

Data transformation service (DTS) packages are groups of SQL Server tasks that involve accessing and changing data...

(such as copying data from one database to another, writing it to a file and bulk inserting records). You can run DTS packages manually, execute them in the context of another script or schedule them as a job in SQL Server Agent. What's not always clear to people, however, is that the context for a DTS package can affect its execution.

When you execute a DTS package manually, as you would with a script or an executable, it runs in your own user context and in the context of the machine running it. For instance, if you have SQL Server Personal Edition Desktop Engine installed on a local machine, and you're using it to manage a server remotely, then any DTS packages executed on your local system will be run in the context of your system. This includes user accounts, file path names and so on.

One common mistake is to create a DTS package that writes data to a file where the path name does not exist on the target computer (or the needed path name may not be documented correctly).

DTS packages that run as a scheduled event or job run in the context of the job owner, not the package creator. Typically, this would be sa, the administrator account for SQL Server. If that's the case, then the DTS package will run in the context of the Windows Service Account. Any conflict between the credentials used to run the package and the credentials needed to write files or read data will cause a problem.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

More information from

  • Ask the Experts: Back up and restore a database in a different server using DTS
  • Tip: Transform data with Microsoft SQL Server 2000 DTS
  • Ask the Expert: Creating a DTS package

  • This was last published in September 2005

    Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

    Start the conversation

    Send me notifications when other members comment.

    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

    Please create a username to comment.