DTS Servers and Schedulers for MS SQL

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.

Latest Articles