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:
Minimum Requirements to build a DTS server
There are many advantages of having a dedicated DTS server. Some of those advantages are:
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.