Don't overlook system context of DTS packages

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

    Requires Free Membership to View

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 SearchSQLServer.com

  • 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 first published in September 2005

    There are Comments. Add yours.

    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    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
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.