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.

By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

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

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

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