solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Idera - SQL diagnostic manager

SQL diagnostic manager is a powerful performance monitoring and diagnostics solution that proactively alerts administrators to health, performance or availability problems within their SQL Server environment via a central console or mobile device. SQL diagnostic manager minimizes costly server downtime by providing agent-less, real-time monitoring and customizable alerting for fast diagnosis and remediation of SQL Server performance and availability problems. SQL diagnostic manager also provides a 'community' environment where, using Idera's IntelliFeed(TM) technology DBAs form a community to collaborate to quickly resolve problems, perform maintenance and capture best practices.

Learn more!

Package execution differences between DTS and SSIS

By: Edgewood Solutions | Read Comments (1) | Print

Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

Related Tips: 1 | 2 | 3 | 4 | More

Problem
With the many changes from SQL Server 2000 to 2005, the Extraction, Transform, and Load process in many respects has changed the most. Even the name changed from Data Transformation Services in SQL 2000 to SQL Server Integration Services in SQL 2005. Not only did the product change, but so did the method for executing packages.

Solution
In SQL Server 2000 and 2005 there are a number of ways to execute a package outside of Enterprise Manager and Query Analyzer:

SQL Server 2000

1. DTSRUNUI

Type dtsrunui from either a command prompt or Run dialog box, which opens the following GUI interface:

This interface gives you the option of executing a package from a SQL Server stored file (in msdb), a Meta Data Services file, or a Structured Storage File (*.dts stored in the File System). If you choose SQL Server or Meta Data Services as the source of the package and have a named instance, replace (local) with the <server_name>\<instance_name>.

You can also obtain the DTS command-line statement by clicking on the "Advanced" button next to the package name:

DTSRUNUI

Then click "Generate" on the bottom right of the window to generate the DTS command. This can then be used to execute the DTS package from a command line.

DTSRUNUI-ADVANCED

2. DTSRUN

Type dtsrun from a Command Prompt (can be run from any command prompt without having to navigate to a particular folder). A series of switches are available:

/S Server Name
/U User Name
/P Password
/E <Use trusted connection instead of /U /P>
/N Package Name
/M Package Password
/G Package GUID String
/V Package Version GUID String
/F Structured Storage UNC filename (overwritten if /S also specified)
/R Repository Database Name <uses default if blank; loads package from repository database>

Package operation (overrides stored Package settings):

/A Global Variable Name:typeid=Value <may quote entire string (including name:typeid)>
/L Log file name
/W Write Completion Status to Windows Event Log <True or False>

DTSRun action (default is to execute Package):

/!X <Do not execute; retrieves Package to /F filename>
/!D <Do not execute; drop package from SQL Server (cannot drop from Storage File)>
/!Y <Do not execute; output encrypted command line>
/!C <Copies command line to Windows clipboard (may be used with /!Y and /!X)

Here is what is seen in the command prompt window when running a package using DTSRUN

DTSRUN-Example

SQL Server 2005

In SQL Server 2005 the GUI and command-line options for package execution are still available, but the syntax and switches have changed somewhat.

1. DTSEXECUI

Type dtexecui from either a command prompt or Run dialog box, which opens the Execute Package GUI interface:

DTSEXECUI

Note that there are many more options in this GUI compared with the one for SQL 2000. If you wish to copy the command-line statement to use in dtexec, click on Command Line section:

DTSEXECUI-Command Line statement

2. DTEXEC

Use dtexec from the Command Prompt by typing in dtsexec (can be run from any command prompt with having to navigate to a particular folder). A series of switches are available (the following information was obtained by typing dtexec /? in the command prompt):