Set up trigger for build success

[SynexusStuart]

All,

This is probably quite a simple one.

We have a build that usually completes at about 1:30 am and a set of schedules and jobs in Cognos Connection which we would like to run once the DM build has successfully completed. At the moment, we schedule these at 3am to make sure the build has finished but they go out even if the build fails. This means we have to re-run teh reports on fixing the build.

I know you can set s job to run in Cognos connection by using a trigger but how do I set up the trigger to pick up the successful completion of a specific build (bulid name = JS_STARS_SQL)

Please help!

Stuart

[MFGF]

Hi,

There are two approaches you could take:
1. Create a jobstream, have the jobstream run the build (hint: set the 'Action on Failure' property to 'Continue'), then follow this with a Condition Node which checks the $RESULT variable - if true, then link on to a Procedure Node which calls the Trigger (use the System() function to call a batch file to fire the trigger).
2. Create a jobstream, have the jobstream run the build (hint: set the 'Action on Failure' property to 'Continue'), then follow this with a Condition Node which checks the $RESULT variable - if true, then link on to an SQL Node which sets a value into a field in a table.  You could then have an Event Studio agent scheduled to check for the existence of this value, and fire off the schedules when found.

Regards,

MF.

[SynexusStuart]

Thanks MF,

We now have a trigger set up and this runs using the Cognos default and is running as System('C:/Cognos/Report.bat').

This works perfectly!

The next problem we have is that we have a job for each day in Cognos (one for Monday, Tuesday etc.), the problem is that if we set these to run by the trigger shown above, they would run every day.

We have set up a different trigger for each day called "ReportMonday" etc. and need the function to be System('C:/Cognos/ReportMonday.bat') on a Monday and System('C:/Cognos/ReportTuesday.bat') on a tuesday etc.

The problem I have is that I can't seem to get today's day into a function in DM as it has limited SQL in-built functions.

Do you know how I can do this? I would rather avoid using Event Studio or recreating the JobStream with a different schedule every day as this would duplicate admin.

Hope you can help.

Stuart

[MFGF]

Hi Stuart,

One solution is to define a UDF in Data Manager to return the current weekday.  Right-click the Functions folder, add a new function called WeekDay, define the return type as CHAR, then on the Implementation tab, use the following expression:

case Mod(DaysBetween(SysDate(),'1899-12-31 00:00:00'),7) of
begin
  0: return 'Sunday';
  1: return 'Monday';
  2: return 'Tuesday';
  3: return 'Wednesday';
  4: return 'Thursday';
  5: return 'Friday';
  6: return 'Saturday';
  default: return 'Unknown day';
end;

You can now call this function from within your Procedure node - use the Concat() function to build up the path of the relevant trigger file into a variable, then use this in your System() function

eg

$Triggerfile := Concat('C:\Cognos\Report', WeekDay(), '.bat');
System($Triggerfile);

Regards,

MF.

[SynexusStuart]

Thanks MF that works a treat.

Oddly though it only works if the Directory is the root folder (i.e. C:\ReportThursday).

This is not a big issue as the local server is pretty secure but can you think of any reason why it doesn't work in the following folder?

C:\Program Files\cognos\c8-DM\datamanager\ReportThursday

If not don't worry,

thanks again
Stuart

[MFGF]

Yep - it's the space in "Program Files" that is throwing it.  You need to modify the expression in the Procedure node so the path contains double-quotes, ie

$Triggerfile := Concat('"C:\Program Files\cognos\c8-DM\datamanager\Report', WeekDay(), '.bat"');
System($Triggerfile);

Cheers!

MF.

[SynexusStuart]

Thanks again.

1 last problem, I promise!!

Everything works fine now when running manually, the problem is when running remotely on a schedule the procedure node returns the following error:

DM-EXP-0003 An expression validation error occurred at position 0
Unknown variable '$Triggerfile'.

DM-JOB-0111 An error occurred validating the node action.

Anything obvious?

Stuart

[MFGF]

Did you define the Triggerfile variable in the properties of your jobstream?  If not, right-click the jobstream, select Properties, go to the Variables tab, add a new variable and call it Triggerfile (the name is case sensitive), set the type to be CHAR and set the Precision(size) to be 50 (or a value long enough to hold the complete path and filename of your batch file).

Cheers!

MF.