DTS Servers and Schedulers for MS SQL

February 23, 2004

DTS is a set of graphical tools and programmable objects that let you extract, transform, and consolidate data from disparate sources into single or multiple destinations that can be bundled and saved as a package and executed. In large organizations, it is a usual practice to keep, schedule and execute all of the DTS packages on a dedicated server. The intent of this article is to provide basic idea on:

• How to create DTS Server
• How to change the DTS package according to the batch schedulers that are used
• Basic steps to create and schedule jobs in Autosys and Smartbatch

Minimum Requirements to build a DTS server

• Windows 2000 Operating System.
• SQL Server 2000 Client.
• All heterogeneous ODBC clients and drivers such as Sybase, Oracle, Mainframe etc.
• Batch Scheduler Client such as Autosys Client and SmartBatch etc. You could also use Windows Scheduler.
• Of course, all the patches and service packs.

DTS Server-Advantages

There are many advantages of having a dedicated DTS server. Some of those advantages are:

• No need to install third party ODBC on the SQL server machines. This eliminates the need to recycle the production SQL Server machine. Sometimes, third party ODBC drivers mess up some DLL files, which is also avoided.
• The batch processing load will be on the DTS server and not on the SQL Server machine.
• Easy to organize and maintain all heterogeneous ODBC DSNs in one machine.
• Easy to organize DTS packages according to different application or groups by saving DTS packages as structure storage file.
• Easy to update ODBC versions and patches
• More secure than keeping the DTS package in SQL Server.

Schedulers

When we use third party Automated Job control systems like Autosys, Smartbatch or Microsoft's Windows scheduler and SQL Job schedules, the DTS package should be modified to suit those Job Schedulers.

Scheduling Jobs in Autosys

Autosys cannot recognize the return code from DTS packages. Usually, Autosys provides a small executable, such as Exitcode.exe, to return values back to Autosys when a job is complete. In order to take advantage of such an executable, we need to change the DTS package to create an indicator file and send the return value based on the existence of the indicator file.