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 2

By Muthusamy Anantha Kumar aka The MAK

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.



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