Executing DTS Packages

After a Data Transformation Services package has been defined and saved it can be executed using:

• The dtsrun command prompt utility.
• SQL Server Agent to run a scheduled job that executes the package.
• The DTS Designer user interface.
• The DTS Export and Import Wizard user interfaces.
• The Execute method of a DTS Package object in a custom application.

When a DTS package is executed, it is loaded into the client application process and executed from that process:

• Packages executed from SQL Server Enterprise Manager run in the context of the Microsoft Management Console (Mmc.exe) process.
• Packages executed from the command prompt using either the dtswiz or the dtsrun utilities run in the context of the command-prompt window.
• Packages run from a custom application run in the context of the process running the application.
• Packages scheduled to run as SQL Server Agent jobs run in the context of the SQL Server Agent process.

The properties of all objects in the package must be valid in the context of the client process that is executing the package. This includes items such as:

• All references to files in file connection objects must be valid from the client process. If drive letters are used in the file path, these must be valid from the client process. If multiple clients will execute a package stored on a file server, it is better to use UNC names in all file names instead of assuming all clients will have the same share drive mappings. For example, use \\FileServerName\DTSShare\Input.txt instead of H:\Input.txt.
• Any executables referenced by any tasks must be present in the PATH environment of the client process.
• The client process must run under a Windows account authorized to open any files specified in the package.
• If the package uses SQL Server integrated security, the Windows account the client process is running under must be authorized to log into all SQL Server databases referenced in the package and must have permissions to access all tables referenced in the package.

See Also