Integrated Error Handling and Email Notification

AnaplanOEG · July 2017

Summary

Anaplan Connect is a command-line client to the Anaplan cloud-based planning environment and is a java-based utility that is able to perform a variety of commands, such as uploading and downloading data files, executing JDBC SQL queries (for importing & exporting data from Anaplan), and running Anaplan actions and processes. To enhance the deployment of Anaplan Connect, it is important to be able to integrate the trapping of error conditions, enable the ability to retry the Anaplan Connect operation, and integrate email notifications. This article provides best practices on how to incorporate these capabilities.

This article leverages the standard Windows command line batch script and documents the various components and syntax of the script. In summary, the script has the following main components:

Set variable values such as exit codes, Anaplan Connect login parameters, and operations and email parameters
Run commands prior to running Anaplan Connect commands
Main loop block for multiple retries
1. Establish a log file based upon the current date and loop number
2. Run the native Anaplan Connect commands
3. Search for string criteria to trap error conditions
4. Branching logic based upon the discovery of any trapped error conditions
5. Send email success or failure notification of Anaplan Connect run status
6. Logic to determine if a retry is required
End main loop block
Run commands post to running Anaplan Connect commands
Exit the script

Section #1: Setting Script Variables

The following section of the script establishes and sets variables that are used in the script. The first three lines perform the following actions:

Clears the screen
Sets the default to echo all commands
Indicates to the operating system that variable values are strictly local to the the script

The variables used in the script are as follows:

ERRNO – Sets the exit code to 0 unless set to 1 after multiple failed reties
COUNT – Counter variable used for looping multiple retries
RETRY_COUNT – Counter variable to store the max retry count (note: the /a switch indicates indicates a numeric value)
AnaplanUser – Anaplan login credentials in the format as indicated in the example
WorkspaceId – Anaplan numerical or named Workspace ID
ModelId – Anaplan numerical or named Model ID
Operation – A combination of Anaplan Connect commands. It should be noted that a ^ can be used to enhance readability by indicating that the current command continues on the next line
Domain – Email base domain. Typically, in the format of company.com
Smtp – Email SMTP server
User – Email SMTP server User ID
Pass – Email SMTP server password
To – Target email address(es). To increase the email distribution, simply add additional -t and the email addresses as in the example.
From – From email address
Subject – Email subject line. Note that this is dynamically set later in the script.

cls
echo on
setlocal enableextensions

REM **** SECTION #1 - SET VARIABLE VALUES ****
	set /a ERRNO=0
	set /a COUNT=0
	set /a RETRY_COUNT=2

	REM Set Anaplan Connect Variables
	set AnaplanUser="<<Anaplan UserID>>:<<Anaplan UserPW>>"
	set WorkspaceId="<<put your WS ID here>>"
	set ModelId="<<put your Model ID here>>"
	set Operation=-import "My File" -execute ^
	              -output ".\My Errors.txt"

	REM Set Email variables
	set Domain="spg-demo.com"
	set Smtp="spg-demo"
	set User="fpmadmin@spg-demo.com"
	set Pass="1Rapidfpm"
	set To=-t "fpmadmin@spg-demo.com" -t "gburns@spg-demo.com" 
	set From="fpmadmin@spg-demo.com"
	set Subject="Anaplan Connect Status"

	REM Set other types of variables such as file path names to be used in the Anaplan Connect "Operation" command

Section #2: Pre Custom Batch Commands

The following section allows custom batch commands to be added, such as running various batch operations like copy and renaming files or running stored procedures via a relational database command line interface.

REM **** SECTION #2 - PRE ANAPLAN CONNECT COMMANDS ***
REM Use this section to perform standard batch commands or operations prior to running Anaplan Connect

Section #3: Start of Main Loop Block / Anaplan Connect Commands

The following section of the script is the start of the main loop block as indicated by the :START. The individual components breakdown as follows:

Dynamically set the name of the log file in the following date format and indicates the current loop number: 2016-16-06-ANAPLAN-LOG-RUN-0.TXT
Delete prior log and error files

Native out-of-the-box Anaplan Connect script with the addition of outputting the Anaplan Connect run session to the dynamic log file as highlighted here: cmd /C %Command% > .\%LogFile%

REM **** SECTION #3 - ANAPLAN CONNECT COMMANDS ***

:START
	REM Dynamically set logfile name based upon current date and retry count.
	set LogFile="%date:~-4%-%date:~7,2%-%date:~4,2%-ANAPLAN-LOG-RUN-%COUNT%.TXT"

	REM Delete prior log and error files
	del .\BAT_STAT.TXT
	del .\AC_API.ERR

	REM Out-of-the-box Anaplan Connect code with the exception of sending output to a log file
	setlocal enableextensions enabledelayedexpansion || exit /b 1
	REM Change the directory to the batch file's drive, then change to its folder
	cd %~dp0
	if not %AnaplanUser% == "" set Credentials=-user %AnaplanUser%
	set Command=.\AnaplanClient.bat %Credentials% -workspace %WorkspaceId% -model %ModelId% %Operation%
@echo %Command%
	cmd /C %Command% > .\%LogFile%

Section #4: Set Search Criteria

The following section of the script enables trapping of error conditions that may occur with running the Anaplan Connect script. The methodology relies upon searching for certain strings in the log file after the AC commands execute. The batch command findstr can search for certain string patterns based upon literal or regular expressions and echo any matched records to the file AC_API.ERR. The existence of this file is then used to trap if an error has been caught. In the example below, two different patterns are searched in the log file.

The output file AC_API.ERR is always produced even if there is no matching string. When there is no matching string, the file size will be an empty 0K file. Since the existence of the file determines if an error condition was trapped, it is imperative that any 0K files are removed, which is the function of the final line in the example below.

REM **** SECTION #4 - SET SEARCH CRITERIA - REPEAT @FINDSTR COMMAND AS MANY TIMES AS NEEDED ***
@findstr /c:"The file" .\%LogFile%  > .\AC_API.ERR
@findstr /c:"Anaplan API" .\%LogFile%  >> .\AC_API.ERR

	REM Remove any 0K files produced by previous findstr commands 
@for /r %%f in (*) do if %%~zf==0 del "%%f"

Section #5: Trap Error Conditions

In the next section, logic is incorporated into the script to trap errors that might have occurred when executing the Anaplan Connect commands. The branching logic relies upon the existence of the AC_API.ERR file. If it exists, then the contents of the AC_API.ERR file are redirected to a secondary file called BAT_STAT.TXT and the email subject line is updated to indicate that an error occurred. If the file AC_API.ERR does not exist, then the contents of the Anaplan Connect log file is redirected to BAT_STAT.TXT and the email subject line is updated to indicate a successful run. Later in the script, the file BAT_STAT.TXT becomes the body of the email alert.

REM **** SECTION #5 - TRAP ERROR CONDITIONS ***
	REM If the file AC_API.ERR exists then echo errors to the primary BAT_STAT log file
	REM Else echo the log file to the primary BAT_STAT log file
@if exist .\AC_API.ERR (
@echo . >> .\BAT_STAT.TXT
@echo *** ANAPLAN CONNECT ERROR OCCURED *** >> .\BAT_STAT.TXT
@echo -------------------------------------------------------------- >> .\BAT_STAT.TXT
			type .\AC_API.ERR >> .\BAT_STAT.TXT 	
@echo -------------------------------------------------------------- >> .\BAT_STAT.TXT
			set Subject="ANAPLAN CONNECT ERROR OCCURED"
		) else (
@echo . >> .\BAT_STAT.TXT
@echo *** ALL OPERATIONS COMPLETED SUCCESSFULLY ***  >> .\BAT_STAT.TXT
@echo -------------------------------------------------------------- >> .\BAT_STAT.TXT
			type .\%LogFile% >> .\BAT_STAT.TXT 	
@echo -------------------------------------------------------------- >> .\BAT_STAT.TXT
			set Subject="ANAPLAN LOADED SUCCESSFULLY"
		)

Section #6: Send Email

In this section of the script, a success or failure email notification email will be sent. The parameters for sending are all set in the variable section of the script.

REM **** SECTION #6 - SEND EMAIL VIA MAILSEND *** 
@mailsend -domain %Domain% ^
			  -smtp %Smtp% ^
			  -auth -user %User% ^
			  -pass %Pass% ^
			  %To% ^
			  -f %From% ^
			  -sub %Subject% ^
			  -msg-body .\BAT_STAT.TXT

Note: Sending email via SMTP requires the use of a free and simple Windows program known as MailSend. The latest release is available here: https://github.com/muquit/mailsend/releases/. Once downloaded, unpack the .zip file, rename the file to mailsend.exe and place the executable in the same directory where the Anaplan Connect batch script is located.

Section #7: Determine if a Retry is Required

This is one of the final sections of the script that will determine if the Anaplan Connect commands need to be retried. Nested IF statements are typically frowned upon but are required here given the limited capabilities of the Windows batch language. The first IF test determines if the file AC_API.ERR exists. If this file does exist, then the logic drops in and tests if the current value of COUNT is less than the RETRY_COUNT. If the condition is true, then the COUNT gets incremented and the batch returns to the :START location (Section #3) to repeat the Anaplan Connect commands. If the condition of the nested IF is false, then the batch goes to the end of the script to exit with an exit code of 1.

REM **** SECTION #7 - DETERMINE IF A RETRY IS REQUIRED ***
@if exist .\AC_API.ERR (
@if %COUNT% lss %RETRY_COUNT% (
@set /a COUNT+=1
@goto :START
		) else (
			set /a ERRNO=1
@goto :END
		)	
	) else (
		set /a ERRNO=0

Section #8: Post Custom Batch Commands

The following section allows custom batch commands to be added, such as running various batch operations like copy and renaming files, or running stored procedures via a relational database command line interface. Additionally, this would be the location to add functionality to bulk insert flat file data exported from Anaplan into a relational target via tools such as Oracle SQL Loader (SQLLDR) or Microsoft SQL Server Bulk Copy (BCP).

REM **** SECTION #8 - POST ANAPLAN CONNECT COMMANDS ***
REM Use this section to perform standard batch commands or operations after running Anaplan Connect commands


:END
exit /b %ERRNO%

Sample Email Notifications

The following are sample emails sent by the batch script, which are based upon the sample script in this document. Note how the needed content from the log files is piped directly into the body of the email.

Success Mail:

Error Mail:

Contributing authors: Pavan Marpaka, Chanaveer Kadapatti, and Scott Smith.