cognos automation

[damzi]

Hello -

I am trying to use the following script to automate the publishing of Powerplay cubes - to refresh old ones and to publish the new ones. It fails to publish after a few times of running. Does any one haev any idea. Would greatly appreciate all help.

REM This script is designed to automate the Publish of new cubes and
REM Refresh old cubes from source folder into which cubes are FTD-ed from Customer (CISC)

REM error trapping
REM FTP log from cube pub to CISC
REM email from lotus notes

Dim z
Const MAX_PATH = 260
Const INVALID_HANDLE_VALUE = -1
Const FILE_ATTRIBUTE_DIRECTORY = &H10
Type FileTime
dwLowDateTime As Long
dwHighDateTime As Long
End Type

TYPE WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As FileTime
ftLastAccessTime As FileTime
ftLastWriteTime As FileTime
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type

Declare Function APIFindFirstFile _
Lib "kernel32" _
Alias "FindFirstFileA" _
(ByVal lpFileName As String, _
lpFindFileData As WIN32_FIND_DATA) as Long

Declare Function APIFindNextFile _
Lib "kernel32" _
Alias "FindNextFileA" _
(ByVal hFindFile As Long, _
lpFindFileData As WIN32_FIND_DATA) As Long

Declare Function APIFindClose _
Lib "kernel32" _
Alias "FindClose" _
(ByVal hFindFile as Long) as Long

Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds as Long)

Sub Process()
Dim FindFileData as WIN32_FIND_DATA
Dim IFindFileData as WIN32_FIND_DATA
Dim hFile as Long
Dim fCount as Long
Dim FilesCountAll as Integer
Dim sLoopFilename as String
Dim iLoopFileName as String
Dim hIFile as Long
Dim bFlag
Dim AppId
Dim MailFilePath as String
Dim LogfilePointer as String

REM Trace Source Directory Path
hFile = APIFindFirstFile("D:\FTP Staging\CISC\Cubes Publish&Refresh\*.*", FindFileData)
LogfilePointer = "D:\FTP Staging\CISC\LogPubStatus\"

On Error Resume Next
KILL "D:\FTP Staging\CISC\LogPubStatus\process.log"
Open "D:\FTP Staging\CISC\LogPubStatus\process.log" for append as #1
write #1, "Process Begins"
write #1,"<TimeStampPubBegin>" & Date & ":" & Time & "</TimeStampPubBegin>"

If hFile <> INVALID_HANDLE_VALUE Then
Do
sLoopFilename = Left$(FindFileData.cFileName, InStr(FindFileData.cFileName, chr$(0)) - 1)
If Not (FindFileData.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) = _
FILE_ATTRIBUTE_DIRECTORY Then

hIFile = APIFindFirstFile("D:\Material to publish R&D\CISC Cubes\*.*", IFindFileData)
'msgbox sLoopFileName
If hIFile <> INVALID_HANDLE_VALUE Then
Do
iLoopFileName = Left$(IFindFileData.cFileName, InStr(IFindFileData.cFileName, chr$(0)) - 1)
If Not (IFindFileData.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) = _
FILE_ATTRIBUTE_DIRECTORY then
'msgbox iloopFileName
If sLoopFileName = iLoopFilename then
bFlag = "True"
exit do
else
bFlag = "False"
end if
End if

Loop Until APIFindNextFile(hIFile, IFindFileData) = 0
APIFindClose hIFile
If bFlag ="True" then

REM Disable Cube in PPES

strCommand = Chr(34) & "D:\Cognos\cer3\bin\ppadmtool.exe" & Chr(34)
strCommand = strCommand & " CONNECT rdcog7app USER administrator PASSWORD admin1234 KILL " & Chr(34) & "/CISC Cubes/" & Left$(sLoopFilename, Len(sLoopFilename) - 4) & Chr(34)

write #1, strCommand

'msgbox strcommand

On error goto Errhndler
z=Shell(strCommand)

'Shell(strCommand)

REM wait a bit for cube to publish

SLEEP 3000
'SENDKEYS "%f", -1
msgbox z

If z=0 then goto Errhndler

REM Copy Updated Cube

FileCopy "D:\FTP Staging\CISC\Cubes Publish&Refresh\" & sLoopFilename, "D:\Material to publish R&D\CISC Cubes\" & sLoopFilename

REM Enable Cube in PPES

strCommand = Chr(34) & "D:\Cognos\cer3\bin\ppadmtool.exe" & Chr(34)
strCommand = strCommand & " CONNECT rdcog7app USER administrator PASSWORD admin1234 ENABLE " & Chr(34) & "/CISC Cubes/" & Left$(sLoopFilename, Len(sLoopFilename) - 4) & Chr(34)

'msgbox strcommand

strCommand = strCommand & ">>D:\FTP Staging\CISC\LogPubStatus\CISC.log"

'msgbox strcommand

write #1, strCommand
On error goto Errhndler
z=Shell(strCommand)

'Shell(strCommand)
SLEEP 3000
'SENDKEYS "%f", -1

If z=0 then Errhndler

REM Write Datetime to Log file

LogfilePointer="D:\FTP Staging\CISC\LogPubStatus\" & Left$(sLoopFilename, Len(sLoopFilename) - 4) & ".PubStatus"
Open LogfilePointer for append as #3
write #3,"<Status>Okay</Status>"
write #3,"<CubeUrl>

http://rdcog7iis/cognos/cgi-bin/ppdsisapi.dll?DC=Q&E=/CISC+Cubes/

& Left$(sLoopFilename, Len(sLoopFilename) - 4) </CubeUrl>"
'write #3,"<CubeViewXxxUrl>

http://"

& sLoopFilename & "</CubeViewXxxUrl>"
'write #3,"<CubeViewYyyUrl>

http://"

& sLoopFilename & "</CubeViewYyyUrl>"
write #3,"<TimeStampPubBegin>" & Date & ":" & Time & "</TimeStampPubBegin>"
write #3,"<Msg></Msg>"
close #3

REM Delete FTP copy of the cube
KILL "D:\FTP Staging\CISC\Cubes Publish&Refresh\" & sLoopFilename

Else
FileCopy "D:\FTP Staging\CISC\Cubes Publish&Refresh\" & sLoopFilename, "D:\Material to publish R&D\CISC Cubes\" & sLoopFilename

REM Publish the cube to Upfront retaining the folder's publish location

strCommand = Chr(34) & "D:\Cognos\cer3\bin\ppadmtool.exe" & Chr(34)
strCommand = strCommand & " CONNECT rdcog7app USER administrator PASSWORD admin1234 PUBLISH " & Chr(34) & "/CISC Cubes/" & Left$(sLoopFilename, Len(sLoopFilename) - 4) & Chr(34)

'msgbox strcommand

write #1, strCommand
On error goto Errhndler
z = Shell(strCommand,0)

SLEEP 2000
'SENDKEYS "%f", -1

If z=0 then Errhndler

REM Write Datetime to Log file

LogfilePointer="D:\FTP Staging\CISC\LogPubStatus\" & Left$(sLoopFilename, Len(sLoopFilename) - 4) & ".PubStatus"
Open LogfilePointer for append as #2
write #2,"<Status>Okay</Status>"
write #2,"<CubeUrl>

http://rdcog7iis/cognos/cgi-bin/ppdsisapi.dll?DC=Q&E=/CISC+Cubes/

& Left$(sLoopFilename, Len(sLoopFilename) - 4) </CubeUrl>"
'write #2,"<CubeViewXxxUrl>

http://"

& sLoopFilename &"</CubeViewXxxUrl>"
'write #2,"<CubeViewYyyUrl>

http://"

& sLoopFilename & "</CubeViewYyyUrl>"
write #2,"<TimeStampPubBegin>" & Date & ":" & Time & "</TimeStampPubBegin>"
write #2,"<Msg></Msg>"
close #2

REM Delete FTP copy of Cube from the FTP folder

KILL "D:\FTP Staging\CISC\Cubes Publish&Refresh\" & sLoopFilename

End If

End If
Bfalg="False"

End if
Loop Until APIFindNextFile(hFile, FindFileData) = 0
End If

APIFindClose hFile

write #1,"Process Ended Successfully"
Close #1

Errhndler:
If z = 0 Then
write #1, "Error in executing the Shell command"
write #1,"Process Ended Unsuccessfully"
APIFindClose hFile
Close #1

REM Execute shell command for email

MailFilePath = "D:\>FTP_Staging\CISC\sendmail_CISC\sendmail_dev.bat "test msg" D:\FTP_Staging\CISC\LogPubStatus\process.log"

'Shell (MailFilePath)

Exit Sub

End If

End Sub

Sub Main ()

Call Process

End Sub



Thnak you

 

[DoubleD]

What error are you getting? At what point in the script is it failing?

Most of us will not have time to validate your entire script. It is much more helpful if you can give specifics regarding the problem you are running into.

Pain is stress leaving the body.

DoubleD

 

[damzi]

Thank you for your quick response. The above script is hugely successful sometimes and at other times returns an error of 'Failed, Object not found' in the Command line prompt. Thats when I know that the cube publish has failed.

I am also trying to hone the above script to fetch and concatenate into a text file all the messages that appear in the command line durimg the automation. The present script returns only a portion of the cmd line messages.

In the following portion of the script -
write #2,"<CubeUrl>

http://rdcog7iis/cognos/cgi-bin/ppdsisapi.dll?DC=Q&E=/CISC+Cubes/

& Left$(sLoopFilename, Len(sLoopFilename) - 4) </CubeUrl>"
-
I am attempting to return the url of the cube published. As you can see it returns the text as is now.

 

[drlex]

I'd also look at adding lines to the errhndler section so as to record the actual error number, message and line at which it occured.

Like DoubleD says, it's hard to take in a script of that length, but I note the use of sleep and SENDKEYS. Is it possible that the timing is out so that the script is getting ahead of itself on occasions and resulting in the error?

lex

soi la, soi carre

 

[damzi]

I am sorry abt the length of the script - am new to the forums. I now realize that I should have only sent out the problem portions of the script. I have added the SLEEP portions becuase the script was getting ahead like you mentioed and it worked.
I am unsure of the script to record the actual error number, message and line of occurance. Would any one have a sample of the same. I thought that I wld not be able to capture the error numbers cause I am using the cmd line to run my application. So I tried to trap all the msgs in the cmd line in the log file for the same - refered to as the Process.log in my script.

 

[drlex]

damzi,
If you put a version of this in your errhndler section to write to file, it could give you assistance in debugging.
"Please note that the macro failed with error " & Err & " : " & Error$ & " which occurred at line: " & Erl

(I run my scripts through Cognosscript, rather than the command line, so perhaps it may not give the same results.)

lex


soi la, soi carre

 

[Draoued]

I don't know which version of PPES you are using but , I know that in most of the case PPADMTOOL doesn't like Space in folder names or cube names , sometime it works and some other it doesn't . And the return line error , is OBJECT Not Found.

  strCommand = strCommand & " CONNECT rdcog7app USER administrator PASSWORD admin1234  PUBLISH " & Chr(34) & "/CISC Cubes/"  & Left$(sLoopFilename, Len(sLoopFilename) - 4)  & Chr(34)

So in this line , I would rather use /CISC[blue]_[/blue]Cubes/ than a space between CISC and blue.

At least in UNIX , PPADMTOOL takes the underscore as a space, so no need to change it in PPES administration.