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.
» 