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
This was first published in September 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation