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.
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.
Prev. page
[1]
2
next page 
