Running an Informatica workflow hourly, but only between specific hours of the day

If you need a workflow to run every day, but only during working hours, for example you need a workflow to only run if the time is between. 8:00 a.m. to 6:00 p.m., try this technique.

Use a Decision task to compare a workflow's start time to a range of hours/minutes you specify.

The general idea is, using a Decision task you compare the workflow start time with a predetermined time, then within the link to the session that occurs after the decision task,  you evaluate the result of the decision and if it is TRUE, the workflow will continue running, else it will end without error.

If you download the example workflow and examine the code, you will notice some unusual logic in the Decision task.  In order to test if the workflow start time is within my range, I have to use the GET_DATE_PART function to get the hour part of the workflow start time. The result of this function had to be converted to character (To_Char function) and appended with a zero (LPAD function) if the time was less than 10.

These cumbersome steps were required so the comparison could be made using hour and not hour/minutes.

I have a sample workflow that demonstrates this technique, you can download a copy here.

If you have Informatica support, you can search the Knowledge base for articles KB 104712 and KB 21253 for their comments about similar solutions.

I would be happy to hear from anyone using this technique or if someone has found an easier, more productive way to accomplish the same thing.

Thank you for your support
 or  PayPal

IMPORTANT,  YOU are responsible for backing up your systems before trying or implementing any suggestions from this blog.  I do not guarantee 100% accuracy in any code examples.  I do not presume to know your system environment(s) or Security requirements; all code examples from this blog should be thoroughly tested before any attempted use on a production system.

5 Response to "Running an Informatica workflow hourly, but only between specific hours of the day"

  1. Informatica Job Support 11/6/17, 5:03 AM
    Hi Jeff,

    Clearness in your post is simply cool and i can assume you’re expert in Informatica. Great, thanks for sharing this post.
  2. Aanal Parikh 11/25/17, 6:00 PM
    If I want to run a workflow once in a day and if that particular schedule is missed due to any reason , job should be retriggered .(anyhow job should be triggered once in 24hrs)
    Any suggestions?
  3. J. Kinzer 11/25/17, 6:35 PM
    This comment has been removed by the author.
  4. J. Kinzer 11/25/17, 6:38 PM
    Aanal Parikh, thank you for your interesting comment. You may need to have a controlling record in a table. For example you could schedule the workflow to run every hour, the first step of the workflow checks the db table for a completion row for today, if there is no row, the the workflow would continue then write a row to the control table to signify today's run is complete, the next scheduled hourly run would start, check the control table, see that the job has ran for the day, then end (without running all the workflow sessions).

    If for some reason a scheduled run is missed or fails, the next hourly run would attempt to run the workflow and would run if there was no control record for today.

    The control Table name could be similar to "LoadStatus" with a column for a table name or workflow name and date or date and time.

    I hope this helps you. If my explanation is not clear contact me again, I could send you a Visio of the flow or give you more detail.

    If anyone else has an alternative solution, please comment.

  5. Arya Bhatt 4/16/18, 4:58 AM
    This comment has been removed by a blog administrator.

Post a Comment