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
» 
Comment and Contribute