DTS Servers and Schedulers for MS SQL

Monday Feb 23rd 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

Learn the basics of creating a DTS server, including how to change the DTS package according to batch schedulers used and the steps required to create and schedule jobs in Autosys and Smartbatch.

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.

Indicator File

It is always advisable to create a small indicator file whenever a DTS package completes. In order to do that, we are going to add a small ActiveX script as the last step in the DTS package as shown below. If the DTS package fails in the middle, the indicator file will not be created.

ActiveX Script



Copy and paste the below ActiveX script code in the ActiveX task window. Make sure you change the values of "sFile" variable to suit your environment. This code will create the indicator file on a specified folder.



'*******************************
'  Visual Basic ActiveX Script
'*******************************
Function Main()
    dim oFSO
    dim x
    dim sFile
    sFile = "C:\MyDTS\MyDTSindicator.txt"
    set oFSO = CreateObject
	   ("Scripting.FileSystemObject")
    set x = oFSO.CreateTextFile(sFile)
    x.WriteLine("Success")
    Main = DTSTaskExecResult_Success

End Function

Save package

Save the DTS package as Structure Storage File as shown below:

Batch File

It is a usual practice to put all the batch processing instructions in a batch file and execute the batch file from scheduler. Copy and paste the below code and save it as a batch file: C:\MyDTS\MyDTS.bat.

ECHO ------------Execution of  MyDTS.Dts started

ECHo DTS_FA_Gaurantee >> C:\MyDTS\MyDTS.log
Date/t >> C:\MyDTS\MyDTS.log
time/t >> C:\MyDTS\MyDTS.log

"c:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe" -F"c:\MyDTS\DTS_FA_Gaurantee.dts" >> C:\MyDTS\MyDTS.log
Date/t >> C:\MyDTS\MyDTS.log
time/t >> C:\MyDTS\MyDTS.log

ECHO ------------Execution of  MyDTS.Dts completed

if not exist C:\MyDTS\MyDTSindicator.txt goto bad
exit
:bad
exitcode 105

Create a job in Autosys by submitting the command shown below:

insert_job: MYDTSPackage1 job_type: c
owner: Myloginname@Domainname
permission: ge,gx
date_conditions: 1
days_of_week: mo, tu, we, th, fr
start_times: "06:05"
description: "Credit Advisor 20 day calendar"
alarm_if_fail: 1
command:"C:\MyDTS\MyDTS.bat"
machine: DTSServer.Americas.domain.com
max_exit_success: 0

Execute the Autosys Job:

sendevent -e FORCE_STARTJOB -J MyDTSPackage1 -P1

Check Status of job in AutoSys:

autorep -J MyDTSPackage1 d

or

autorep -J MyDTSPackage1 q

Check Log File

Open the file in C:\MyDTS\MyDTS.log using notepad. You can see all of the steps including date and time the job started and finished.

Scheduling Jobs in SmartBatch

SmartBatch cannot recognize the return code from DTS packages. In order to make smartbatch recognize whether a DTS package was successfully executed or not, we have to create a indicator file.

Indicator File

It is always advisable to create a small indicator file whenever a DTS package completes. In order to do that, we are going to add a small ActiveX script, (shown below), as the last step in the DTS package. If the DTS package fails in the middle, the indicator file will not be created.

ActiveX Script


Copy and paste the below ActiveX script code in the ActiveX task window. Make sure you change the values of "sFile" variable to suit your environment. This code will create the indicator file on a specified folder.



'*******************************
'  Visual Basic ActiveX Script
'*******************************
Function Main()
    dim oFSO
    dim x
    dim sFile
    sFile = "C:\MyDTS\MyDTSindicator.txt"
    set oFSO = CreateObject
	  ("Scripting.FileSystemObject")
    set x = oFSO.CreateTextFile(sFile)
    x.WriteLine("Success")
    Main = DTSTaskExecResult_Success

End Function

Save package

Save the DTS package as Structure Storage File, as shown below.

Create Job in Smartbatch

Smartbatch is organized into Operations, Steps, Jobs and Schedules. Create Operation to execute the DTS package as shown below:

Operation Name: OP_MyDtsPackage1

Operation:

Cmd/c " Cmd/c ""c:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe" -F"c:\MyDTS\DTS_FA_Gaurantee.dts" >> C:\MyDTS\MyDTS.log

Description: Import Data from Sybase

Notification Logging - Success: OP_MyDtsPackage1 Successful

Notification Logging - Failure: OP_MyDtsPackage1 failed

Create another operation to check the indicator file as shown below.

Operation Name: MyDtsPackage1Indicator

Operation:

cmd /c if not exist C:\MyDTS\MyDTSindicator.txt echo g | c:\winnt\CHOICE /N /C: 1234567890abcdefg > NUL

Description: Check Indicator file

Notification Logging - Success: MyDtsPackage1Indicator Successful

Notification Logging - Failure: MyDtsPackage1Indicator failed

Create Job "Job_MyDTSPackage1" and then create step "ST_MyDTSPackage1" and make as a job step for "Job_MyDTSPackage1." Add the above two operations to the step ST_MyDTSPackage1 and create Schedule "SCH_MyDTSPackage1" to run the day and time you would like the DTS package to run. Assign this schedule, "SCH_MyDTSPackage1," to the job "Job_MyDTSPackage1".

When you execute the job from the Smartbatch GUI, you can see the job failure or success status on the status screen. You can also see the log by opening the C:\MyDTS\MyDTS.log file.

Conclusion:

As Mentioned before, the intent of this article is to provide a basic idea on how to create a DTS server, how to change the DTS package according to batch schedulers used and the basic steps to create and schedule jobs in Autosys and Smartbatch.

If the emailing feature is not enabled in Autosys or Smartbatch, you can include this feature of sending a failure email message by adding the below VBScript below, modifying the email address, naturally.

'Sending a text email using a smtp server  

Set objMessage = CreateObject("CDO.Message") 
objMessage.Subject = "MyDTSPackage Failed" 
objMessage.Sender = "mak_999@yahoo.com" 
objMessage.To = "mak_999@yahoo.com" 
objMessage.TextBody = "MyDTSPackage Failed" 
objMessage.Configuration.Fields.Item _ 
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
objMessage.Configuration.Fields.Item _ 
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mycompany.net" 

objMessage.Configuration.Fields.Item _ 
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 
objMessage.Configuration.Fields.Update 
objMessage.Send

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved