DTS on the Move

As I've learned during the 5 years of Data Transformation Services (DTS) programming I've done since SQL Server 7.0 shipped, one of the most important goals in creating a DTS package is to build optimum portability into the design. Optimum portability means that a package can execute properly on virtually any server. If you design a package with portability in mind, you'll have to do little or no modification when you move the package from one server to another. Portability is important because most developers design new packages on development servers, then move the final, tested version to a production server. If you don't make all the server-dependent modifications before moving your package to the production server, the DTS package won't execute properly. By following the tips I cover here, you can reduce the number of modifications required when moving a package and minimize your troubleshooting time.

Let's examine seven ways to achieve optimum portability when you program DTS packages. We'll look at the two most overlooked issues involving portability: component localization and runtime security. Then, we'll dig into using global variables with the Dynamic Property Task to enable dynamic changes at runtime, the drawbacks of using the Send Mail Task, using Universal Naming Convention (UNC) paths, storage of and access to source and destination files, and finally, using Disconnected Edit to modify DTS package attributes.

Related:  DTS Packages Sometimes Make Moving Data Tricky

1. Localize All Components

Component localization means that all the workings your DTS package requires must be present on the local system. This is important because a DTS package executes on the system from which you initiate it. For example, if you initiate a DTS package from your workstation, the processing takes place locally. Therefore, all objects, third-party add-ins, ODBC or OLE DB drivers, and access to data sources must be available on all workstations and servers from which a DTS package might be executed. If any of these things are missing from the initiating system, you'll get errors such as Unable to create object.

2. Ensure Runtime Security

Although a package's definition might be stored in SQL Server, at runtime, packages run externally from SQL Server through either the DTSRun or the DTSRunUI utility. When running through these client executables, DTS packages assume the security context of the user who initiates the package. This rule is important to remember because it also extends to packages that run as a scheduled job through the SQL Server Agent. Specifically, if a package's schedule was created by a user who belongs to the SQL Server sysadmin security role, that package assumes the security context of the SQL Server Agent service. A package whose schedule was created by a user who doesn't belong to the sysadmin role assumes the context of the user who created the schedule. Therefore, to avoid runtime security conflicts, you need to ensure that each user's role memberships—or more importantly, the security roles of the person or process executing and scheduling the DTS package—are consistent across your development, testing, and production environments.

3. Use Global Variables and the Dynamic Property Task

(SQL Server 2000 only)
What complex DTS package is complete without the use of a few ActiveX scripts? ActiveX scripts perform data transformations, control the package flow and execution, and can test for the existence of certain package conditions. Most ActiveX scripts use variables extensively, but if you find that many of your tasks reference the same variable, consider using package global variables instead of local ones. You can reference package global variables in the same way that you reference local script variables. Moreover, by using global variables, you can centrally manage script variables, eliminating the need to modify multiple scripts when just one script variable needs changing.

By design, package global variables are static values, but you can make them dynamic by using the DTS Dynamic Property Task. Global variables are listed in the Browser section of the Package Object Browser; you can add them by using the syntax DTSGlobalVariables("XYZ").Value, where XYZ is the name of the global variable.

The Dynamic Property Task is possibly the most powerful DTS task. With this task, you can set or modify attributes for all package objects—tasks, steps, lookups, connections, and global variables. The Dynamic Property Task can set attributes based on values from a variety of sources; for instance, it can set package attributes based on results from a database query, entries from an INI file, or a constant value. Alternatively, you can use a data file, a constant, or a value of a global variable to set object attributes from a Dynamic Property Task.

By using a Dynamic Property Task and global variables, you can design DTS packages so that little or no modification is necessary as runtime conditions (such as where the package is running) change. For example, you can use a combination of the Dynamic Property Task and global variables to derive the current fiscal period and year of your financial systems. If the current fiscal year and month are stored in a table in the financial system, you can use a database query to look up these values so that you don't need to hard-code the values in the package. Otherwise, if the package can't dynamically assign these values, you have to modify the package every month.

4. Standardize Email and Outlook Profiles

A major portability concern when you're using the DTS Send Mail Task for messaging is its reliance on SQL Mail. Specifically, SQL Mail requires the configuration and testing of an Outlook profile under the SQL Server service's startup account. Therefore, to minimize package-definition changes when you're moving from one environment to another, a consistently named and configured Outlook profile must exist on each Windows Server hosting SQL Server and the DTS package before deployment. If you execute a DTS package on a server that has a different name for its mail profile, the task will fail. For example, if the mail profile that SQL Mail uses is named Admin on one server and Admin1 on the second server, the task won't execute properly from the second server. In addition, if a user initiates the Send Mail Task from a workstation, it will likely fail because the mail profile on the workstation probably has a different name.

Further complicating the use of the DTS Send Mail Task are security and localization. As I mentioned, packages assume the security context of the executing user. However, because SQL Mail uses an Outlook profile established on the Windows Server under the SQL Server service startup account, for your package to execute properly, you can run it only from the server. Even then, you can run it only under the same security account as SQL Server.

To overcome this portability limitation, you can use a DTS ActiveX Script Task with CDONTS to send email notifications. CDONTS is the object model that the Microsoft IIS SMTP service uses to send mail; it's automatically installed on Windows 2000 Servers running IIS 5.0 and the SMTP service. CDONTS doesn't rely on profiles, so it eliminates both the security and localization dependencies introduced by the DTS Send Mail Task.

The only drawback to this solution occurs when you attempt to execute a package from a workstation. By default, CDONTS isn't available under Win2K Professional; instead, it must be manually installed. Listing 1 shows a simple script that uses CDONTS to send mail. I use this method extensively to send failure notifications to my pager. In production, you might want to remove the last line of the script so that the package doesn't wait for the prompt to be acknowledged. This line of the script is primarily for troubleshooting.

5. Use Universal Naming Conventions

Using Universal Naming Conventions (UNC) paths can eliminate unnecessary troubleshooting efforts and make your package more portable. When providing paths to source and destination files, always use a UNC path instead of drive letters. Doing this is important because the likelihood of a server having the same drive-letter mappings as your workstation is very low. If you use drive letters instead of UNC paths, your DTS package will fail because it won't be able to locate the needed files. To use a UNC path, simply replace the drive letter with the server name. For example, you'd replace G: with \.

6. Centralize Data Files

As I mentioned earlier, when a DTS package is executing, it runs under the security context of the person or process executing the package. Therefore, the security context, be it through the user's account or through the SQL Server Agent service's startup account, must have appropriate permissions to read from source files and write to destination files. To reduce administrative overhead, consider using one folder on one server for all data files. By centralizing data files to a single server and folder, you can easily manage security access. If data files are in multiple locations, you have to manage the security for all source and destination folders separately. By keeping data files in one place, you know exactly where to locate your source and destination files, thus reducing the time you spend looking for them. If you need to separate a test file from a production file, simply create a separate folder in your main directory.

7. Use Disconnected Edit

(SQL Server 2000 only)
You can't always program a DTS package so that it's 100 percent portable. This means that occasionally, you'll need to make some design-time modifications to tasks by using the Package Designer before you can move a package from one server to another. To make these modifications, consider using DTS's Disconnected Edit feature.

When you're editing certain properties in certain tasks in the Package Designer, the designer will reset all dependent properties. For example, if you reset the Destination Server in a Transform Data Task, the Package Designer might reset any transformations referencing that server, and you'll lose all your development work. With Disconnected Edit, you can edit these properties at design time without affecting any dependent attributes.

Disconnected Edit differs from a Dynamic Property Task in that you use the Disconnected Edit feature at design time, whereas you use the Dynamic Property Task to modify package attributes at runtime. Disconnected Edit is similar to editing the Windows registry—mistakes you make in using this feature can break the package and cause it not to function properly. Therefore, be sure to back up the package before making any changes.

Also note that Disconnected Edit doesn't validate changes. For example, when you change a value for a data-source destination, SQL Server doesn't validate the change against the data source. So if the data source doesn't exist or the login information is incorrect, the Disconnected Edit feature won't alert you to the mistake. If your modifications are incorrect, the package might not function properly.

Keep It Moving

By using these solutions, you can program DTS packages that have maximum portability while increasing programming consistency. These suggestions should help reduce your troubleshooting efforts and ensure that your DTS packages function properly on more than one server. I learned these lessons the hard way—now you don't have to.