Administering and Editing a DTS Package

The good news is that even though the interface may not be superb, you can still edit, run, and schedule packages inside SQL Server 2005. You can do this through Management Studio. Assuming you upgrade an existing SQL Server 2000 instance to SQL Server 2005, your DTS packages will be imported into the new SQL Server 2005 instance as DTS packages as well. They would appear in Management Studio when connected to the Database Engine under Management Legacy Data Transformation Services (shown in Figure 11-1). Any package would remain intact, except for those stored in the Metadata Repository.

Figure 11-1: Data Transformation Services shown in Object Explorer

You can open any package by right-clicking the package and selecting Open. However, in order to perform this task, you must have Microsoft SQL Server 2000 DTS Designer Components installed, which is part of the feature pack. To download this component, search for the SQL Server 2005 Feature Pack with your favorite search engine. The feature pack includes many additional tools that didn’t make the installation DVD for SQL Server (such as the designer components, the DTS runtime, and some additional drivers for SSIS like DB2).

When you open the package, you’ll be able to edit the package just like you would in SQL Server 2000. Unfortunately, the screen is modal, so you won’t be able to do anything else until you close the designer. You can also right-click the Data Transformation Services folder and select Open Package File to open a .DTS file that’s on the file system.

Another important item that you can do is import a package from a .DTS file. To do this, right-click the Data Transformation Services folder and select Import Package File. You’ll then point to the package file and it will be imported into the Data Transformation Services folder. To execute a package, simply open it and click the Run button. Features like DTS package logs have not been implemented in SQL Server 2005. You also cannot create a new package unless you modify an existing package and click Save As to save it with a new name.

To schedule a DTS package in SQL Server 2005, the normal simple tactic of right-clicking the package and selecting Schedule no longer works. You will instead have to schedule it manually or employ a little trick to schedule the package. Your goal is to have the scheduler execute the package by using DTSRun.exe from a SQL Server Agent CMDExec job step.

You can either type this DTSRun.exe command manually, or you can open the DTSRun.exe wrapper, which is called DTSRunUI.exe. This wrapper program is hidden in the SSIS bin directory and can be accessed from a Run command in Windows.

In DTSRunUI.exe, point to a package on your SQL Server 2005 machine and the rest of the options will become available, as shown in Figure 11-2. For example, in this screen, you can run a package by clicking the Run button. You can also schedule the package in SQL Server 2005 by clicking Schedule. The package will be scheduled and the full DTSRun.exe command will be encrypted.

Figure 11-2: DTSRun.exe wrapper

You can click the Advanced button to see much more detail about your DTS package and to see advanced options. For example, you can set the global variables of the package as shown in Figure 11-3. Most importantly, you have the ability to click the Generate button to generate the exact DTSRun.exe command to execute for a SQL Agent job. You can also encrypt the command by selecting the Encrypt the command option.

Figure 11-3: Detail about your DTS package and advanced options

If you choose to generate a command from DTSRunUI.exe manually, enter the DTSRun.exe command, and the steps for scheduling the package look much the same. To schedule the package, create a new job in SQL Server Agent. When you create a new step, specify that the type of step is an Operating System (CmdExec) command. After you specify the type, paste in the command from DTSRunUI.exe and you’re ready to go (see Figure 11-4).

Figure 11-4: Pasting in the command from DTSRunUI.exe

Following is a simple command unencrypted. This command runs the Simple Load package on the brianknight server. The /G switch is optional and specifies the package GUID. The /A commands specify the input global variables that you may want to set.

  DTSRun /S "brianknight" /N "Simple Load" /G "{8742BBB1-B6C5-4778- A734-50A1E5BEFDD2}" /A "strFileName":"8"="C:\ExpertSSIS\ZipCodeExtract.csv" /A "strServerName":"8"="brianknight" /W "0" /E