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:\<sharename> with \\<servername>\<sharename>.
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.
End of Article
Prev. page
1
[2]
next page -->
