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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 23, 2004

DTS Servers and Schedulers for MS SQL - Page 3

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date