Creating restartable jobs (resume after failure)

This example demonstrates how to create a single-script job that can restart at the point where it failed on previous run without need to repeat successful procedures. Note that a more efficient way is to create each procedure as a separate job  then link them together using Job Dependencies Editor (see example).

// This job  runs every 15 minutes. It uses FTP commands to download files from one of the company's
// FTP servers. After all files successfully downloaded, it runs BCP utility for each file to load data
// into SQL Server. After all files successfully loaded  into database, it executes 2 database stored
// procedures that recalculate current positions.
// After every successful step this jobs updates the status value stored in .INI file. If this job fails
// on any step it will resume starting from the failed step next time it will run.
// There are 4 steps that have to be completed:
//                        Step 1: verify file presence on FTP server
//                        Step 2: FTP (download) files from FTP server
//                        Step 3: BCP (load) files into database
//                        Step 4: Execute database stored procedures that recalculate current positions


// check .INI file for the current status
Dim status, string
Dim new_cycle, boolean
INIFileGetKey( "C:\LOAD\DB_LOAD.INI", "DB Load", "Status", status )
isEqual( status, "Done", new_cycle )
// if status is "Done" then this job was successfully completed last time and now
// it should start a new load

if( new_cycle, START_NEW_CYCLE, CHECK_FTP_FILES )
START_NEW_CYCLE:
        Set status, "FTP File Check"
        INIFileSetKey( "C:\LOAD\DB_LOAD.INI", "DB Load", "Status", status )

CHECK_FTP_FILES:
       //////////////////////
       //       Step 1       //
       //////////////////////
       Dim ftp_check, boolean
       Dim found, boolean
        isEqual( status, "FTP File Check", ftp_check )
        // if status is "FTP File Check", check for files on the FTP server, otherwise skip this step
        if(  ftp_check, CHECK_ACCOUNTS, FTP_GET_FILES )

// ... check for files on the remote FTP site
CHECK_ACCOUNTS:
        FTPFileExists( "ftp.myserver.com", "myname", "mypassword", "account.dat", found )
        if( found, CHECK_HOLDINGS, RETRY_LATER )
CHECK_HOLDINGS:
        FTPFileExists( "ftp.myserver.com", "myname", "mypassword", "holding.dat", found )
        if( found, CHECK_TRADES, RETRY_LATER )
CHECK_TRADES:
        FTPFileExists( "ftp.myserver.com", "myname", "mypassword", "trade.dat", found )
        if( found, FTP_DOWNLOAD, RETRY_LATER )

FTP_DOWNLOAD:
       //////////////////////
       //       Step 2       //
       //////////////////////
        Set status, "FTP Download"
        INIFileSetKey( "C:\LOAD\DB_LOAD.INI", "DB Load", "Status", status )

FTP_GET_FILES:
        Dim ftp_pending, boolean
        isEqual( status, "FTP Download", ftp_pending )
        // if status is "FTP Download", download files from the FTP server, otherwise skip this step
        if(  ftp_pending, START_FTP, BCP_FILES )
START_FTP:
        FTPGetFile( "ftp.myserver.com", "myname", "mypassword", "account.dat", "C:\LOAD\ACCOUNT.DAT" )
        FTPGetFile( "ftp.myserver.com", "myname", "mypassword", "holding.dat", "C:\LOAD\HOLDING.DAT" )
        FTPGetFile( "ftp.myserver.com", "myname", "mypassword", "trade.dat", "C:\LOAD\TRADE.DAT" )
        // remove files from the FTP server to prevent loading of the same files more than once
        FTPDeleteFile( "ftp.myserver.com", "myname", "mypassword", "account.dat" )
        FTPDeleteFile( "ftp.myserver.com", "myname", "mypassword", "holding.dat" )
        FTPDeleteFile( "ftp.myserver.com", "myname", "mypassword", "trade.dat" )
       

BCP:
       //////////////////////
       //       Step 3       //
       //////////////////////
        Set status, "BCP"
        INIFileSetKey( "C:\LOAD\DB_LOAD.INI", "DB Load", "Status", status )

BCP_FILES:
        Dim bcp_pending, boolean
        isEqual( status, "BCP", bcp_pending )
        // if status is "BCP", BCP files to database, otherwise skip this step
        if(  bcp_pending, START_BCP, CALC_POSITIONS )
START_BCP:
        Dim process, number
        // truncate database tables before loading
        RunAndWait("isql -Ssqlserver -Uuserid -Ppassword < TRUNCATE_TABLES.SQL, "C:\LOAD\", 60, process )
        // BCP files into database
        RunAndWait("bcp dataware..account in ACCOUNT.DAT -Ssqlserver -Uuserid -Ppassword > BCP.LOG, "C:\LOAD\", 1200, process )
        RunAndWait("bcp dataware..holding in HOLDING.DAT -Ssqlserver -Uuserid -Ppassword > BCP.LOG, "C:\LOAD\", 1200, process )
        RunAndWait("bcp dataware..trade in TRADE.DAT -Ssqlserver -Uuserid -Ppassword > BCP.LOG, "C:\LOAD\", 1200, process )

DB_PROC:
       //////////////////////
       //       Step 4       //
       //////////////////////
        Set status, "Stored Procedures"
        INIFileSetKey( "C:\LOAD\DB_LOAD.INI", "DB Load", "Status", status )

PROCESS_DATA:
        Dim sp_pending, boolean
        isEqual( status, "Stored Procedures", sp_pending )
        // if status is "Stored Procedures", execute db procedures, otherwise skip this step
        if(  sp_pending, START_SP, DONE )
START_SP:
        Dim process, number
        RunAndWait("isql -Ssqlserver -Uuserid -Ppassword < RUN_PROC.SQL, "C:\LOAD\", 1800, process )

DONE:
        // all done
        INIFileSetKey( "C:\LOAD\DB_LOAD.INI", "DB Load", "Status", "Done" )
        exit

RETRY_LATER:
        // one of required files is missing, so don't update the status and retry in next 15 minutes.