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 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

    Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    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:

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close