I was looking for a function to read the time-stamp on a source file that's used to update a
cube several times during the day. If the chore 'looks' at the file and sees that it's late - ie:
skipped a drop file, then send an email alert to admin user.
Couldn't find any DateTimeStamp functions within TM1 so I used a long way:-
* ExecuteCommand() a vbs -> write-out timestamp to a text file
* Read text file to a parameters cube to record the times
* TI: read parameters and send email
There's probably a much better solution so if anyone knows of a pre-built function,
or find any flaws, pls let me know.
the *.vbs script
Code: Select all
On Error Resume Next
sPath = "\\serverName\FTPdata\SAPftp"
i = 0
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder(sPath)
Set iTxt = fso.CreateTextFile(sPath & "\TM1_timeKeeper.cma", True, False)
dtEnd = Now()
For Each fn In fldr.Files
If fn.Name Like "*.txt" Then
i = i + 1
fnDts = fn.DateLastModified
dtStart = DateValue(fnDts) + TimeValue(fnDts)
fnDtIntv = DateDiff("n", dtStart, dtEnd) - (DateDiff("h", dtStart, dtEnd) * 60)
iTxt.WriteLine fn.Name & "," & fn.DateLastModified & "," & i & "," & fnDtIntv
End If
Next
iTxt.Close
Code: Select all
562,"CHARACTERDELIMITED"
586,"\\serverName\FTPdata\SAPftp\TM1_timeKeeper.cma"
585,"\\serverName\FTPdata\SAPftp\TM1_timeKeeper.cma"
#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
# PROLOG
fPathFile = 'E:\TM1\Admin\dw_ComposeMail.txt';
cYear = CellGetS('Parameters', 'Plan', 'wCurrentYear');
CheckSum = CellGetN('General Ledger', 'EBIT', 'ALLCCTR', 'All Entities', 'Total Year', cYear, 'Actual', 'Amount');
If (roundp(CheckSum, 2) < 0.50 & roundp(CheckSum, 2) > -0.50);
ASCIIOutput(fPathFile, NumberToString(CheckSum));
Else;
sRecipient = 'yyi@mydomain.com.au';
sSubject = 'SAP GL - Trial Bal';
sMessage = 'Balance = ' | NumberToStringEx(CheckSum, '#,##0.00', '.', ',');
S_Run = 'cmd /c E:\TM1\Admin\SendMail.vbs' | ' "' | sRecipient | '"' | ' "' | sSubject | '" ' | '"' | sMessage | '"';
ExecuteCommand(S_Run,0);
EndIf;
S_Run = 'cmd /c E:\TM1\Admin\sapFtpTimeKeeper.vbs';
# try 1 for file lock
ExecuteCommand(S_Run,1);
# --------------------------------------------------------------------
#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
# METADATA
574,16
#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
# DATA
If ( vFileName @= 'TM1_ACTUALS.txt' );
CellPutS(vTimeStamp, 'Parameters', 'A', 'sapGLFile');
CellPutS(vFileName, 'Parameters', 'B', 'sapGLFile');
CellPutN(vLapse, 'Parameters', 'A', 'sapGLTimeDiff');
EndIf;
# --------------------------------------------------------------------
575,24
#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
# EPILOG
timeStamp_Now = TimSt (Now, '\h:\i:\s');
timeStamp_File = CellGetS('Parameters', 'A', 'sapGLFile');
timeStamp_Lapse = CellGetN('Parameters', 'A', 'sapGLTimeDiff');
If (timeStamp_Lapse > 9);
sRecipient = 'yyi@mydomain.com.au';
sSubject = 'SAP - FTP Fail ' | timeStamp_Now;
sMessage = 'Time = ' | timeStamp_File | ' ~ ' | NumberToString(timeStamp_Lapse) | ' mins';
S_Run = 'cmd /c E:\TM1\Admin\SendMail.vbs' | ' "' | sRecipient | '"' | ' "' | sSubject | '" ' | '"' | sMessage | '"';
ExecuteCommand(S_Run,0);
EndIf;