Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 23, 2004

DTS Servers and Schedulers for MS SQL

By Muthusamy Anantha Kumar aka The MAK

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.


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM