Informatica Looping

user_2874997 0
Viewed 228

I am looking for information on looping in Informatica. Specifically, I need to check if a source table has been loaded, if it has, move to next step, if not wait X minutes and check the status table again. I would prefer direction to a place I can learn this on my own, but I need to confirm this is even possible as I have not found anything on my google searches.

informatica
2 Answers

You can use a simple shell script to do this wait and watch capability.

#/bin/sh
# call it as script_name.sh 
# it will wait for 10 min and check again for data, in total it will wait for 2hours. change them if you want to
# Source is assumed as oracle. change it as per your  source.

interval=600
loop_count=10
counter=0
while true
do
    $counter=`expr $counter + 1 `
    db_value=`sqlplus -s user/pass@local_SID <<EOF
    set heading off
    set feedback off
    SELECT count(*) FROM my_source_table;
    exit
    EOF`;
   if [ $db_value -gt 0 ]; then
      echo "Data Found."
      exit 0
   else
      if [ $counter -eq $loop_count ]
      then 
         echo "No data found in source after 2hours"
         exit 1
      else
         sleep $interval
      fi
   fi
done

And add this shell script(in a CMD task) to the beginning of the workflow.
Then use informatica link condition as if status= 0, proceed else email that wait time is over.

You can refer to the pic below. This will send a mail if wait time is over and still data is not there in source.

whole Wkflow

user_64305230

In general, looping is not supported in Informatica PowerCenter.

One way is to use scripts, as discussed by Koushik.

Another way to do that is to have a Continuously Running Workflow with a timer. This is configurable on Scheduler tab of your workflow:

enter image description here

Such configuration makes the workflow start again right after it succeeds. Over and over again.

Workflow would look like:

Start -> s_check_source -> Decision -> timer
                                   |-> s_do_other_stuff -> timer

This way it will check source. Then if it has not been loaded trigger the timer. Thet it will succeed and get triggered again. If source turns out to be loaded, it will trigger the other session, complete and probably you'd need another timer here to wait till next day. Or basically till whenever you'd like the workflow to be triggered again.

user_28340650
Related