Informatica Workflow Successful : No Data in target !

This is a frequently asked in the Informatica forums and the solution is usually pretty simple. However, that will have to wait till the end because there is one important thing that you should know before you go ahead and fix the problem.

Your workflow should have failed in the first place. If this was in Production, Support Teams should know that something Failed. Report users should know the data in the Marts is not ready for reporting. Dependent workflows should wait until this is resolved. This coding practice basically violates the Age-old Principle of fail-fast when something goes wrong, instead of continuing flawed execution pretending “All is well”, causing the toughest-to-debug defects.

Of Course, this is not specific to Informatica. It is not uncommon to see code in other languages which follows this pattern. The only issue that is specific to Informatica is that this is the default behavior when you create a session. So you might have this “bug” in your code without even knowing it.

Stop On Errors:

Indicates how many non-fatal errors the Integration Service can encounter before it stops the session. Non-fatal errors include reader, writer, and DTM errors. Enter the number of non-fatal errors you want to allow before stopping the session. The Integration Service maintains an independent error count for each source, target, and transformation. If you specify 0, non-fatal errors do not cause the session to stop.
Optionally use the $PMSessionErrorThreshold service variable to stop on the configured number of errors for the Integration Service.

In Oracle, it is the infamous “when others then null” .

In Java..Something like..

The solution to this problem in Informatica is to set a limit on the number of allowed errors for a given session using one of the following methods.
a) Having “1” in your default session config : Fail the session on the first non-fatal error.
b) Over-write the session Configuration details and enter the “Stop On Errors” to “1” or a fixed number.
c) Use the $PMSessionErrorThreshold variable and set it at the integration service level. You can always override the variable in the parameter file. Take a look at this Article on how you can do that.

Remember, if your sessions do not belong to one of these categories, you are doing it wrong!.
a) Your session Fails and Causes the workflow to fail whenever any errors occur.
b) You allow the session to continue despite some (expected) errors, but you always send the .bad file and the log file to the support/business team in charge.

Why is there no data in Target

The solution to “why the records didn’t make it to the target” is usually pretty evident in the session log file. The usual case (based on most of the times this question is asked) is becuase all of your records are failing with some non-fatal error.

The only point of this article is to remind you that your code has to notify the right people when the workflow did not run as planned.

Cheers!
Rajesh

  • mani

    Hey Rajesh,

    Really its a good site.
    And very useful postings by you..

    Keep posting..

    All the best….:-)
    Mani

  • Raghuvansh

    Rajesh,

    Thanks alot for the above useful information.

    Regards,
    Raghuvansh.

  • Raghuvansh

    Rajesh,

    I have a question,what if there is no record in the source file/table and “Stop on Error” is set to 1. Workflow will fail or run successfully with out any failure ??

    Regards,
    Raghuvansh.

    • Raghuvansh

      I had this question in mind coz in this case when we have no records in Source, there wont be any fatal error so it shouldn’t create any issue.

    • Rajesh

      In that case, there is no “error” , so the workflow would be complete successfully with zero rows inserted. Which is what we would expect to happen.

      -Rajesh.

  • Raghuvansh

    Rajesh,

    So what should be done to avoid this scenario, I mean if no data is there in suppose 2 out of 10 sources. What functionality can we add so that this workflow fails, coz if this succeeds and create and empty target, then it is a huge issue.

    Please suggest.

    • Rajesh

      @Raghu,

      If there is no data in the source, it is not really an error. so, the workflow should (technically) succeed, even though there are zero rows inserted .

      If you want to wait till the load is complete on your source table (let’s say you want your dimension table be loaded completely before the fact is loaded), then you should use event waits and make sure the second workflow/session waits till the first one is complete. Check these links on how you can implement this.
      http://www.etl-developer.com/2011/01/work-flow-dependency-in-informatica-event-wait-files/
      http://www.etl-developer.com/2011/04/workflow-dependency-in-informatica-part-2-load-status-entries/

      Thanks,
      Rajesh.

      • Raghuvansh Gosain

        Rajesh,

        I gone through both the links but found not so relevant. Actually case scenario is,
        There is a workflow which has series of session in it, Second session is the one for which I am concerned. It creates a file for which I want to be notified when it is empty.

        Can I add some functionality to the very next session to make this happen.

        • Rajesh

          You can add a command task (which calls a script) after the second session to check the generated file.
          You can check for the existence of the file ( -f file_name exists) and if it does, check the number of lines in the file (wc -l in unix).
          you can use unix mail to send an email if the file does not exist or if it is empty.

          If you want to fail the workflow if the file is empty, return a non-zero return code in the script and select “Fail parent if task fails” in the command task properties.
          If you want to make sure the third session runs only of the file has non-zero rows, connect it as (session1 -> cmdTask1 -> session2) and use ‘.status=succeeded” for all sessions.

          • Raghuvansh Gosain

            thanks a lot Rajesh, I will try this thing in Dev env and let you know about progress.

  • Ankit

    Hi Rajesh,

    This is really a gr8 job, a lot of knowledge we gather through here…keep posting some more real time scenarios and issue handling, with troubleshooting examples, as this will help us a lot in our work as well. I really appreciate your intiation.

    Thanks
    Ankit

  • sushil

    hi rajesh

    can u post an article on target update override?

    another scenario for u also for which i faced so many difficulties.But still not able to do that one

    load half data on each run??

    • Rajesh

      Sushil – I have not come across scenarios where the only option was to use a target override.
      Some code I have see uses target override when the informatica connection is different from the table owner. Eg. The table might be DATAMRT.SALES_SUMMARY, but you connect using ETLUSER.
      We actualy don’t need the override for this. You can mention the owner in the target instance setting and the generated SQL statement will be correct.

      “load half data on each run??” — Why? I have seen this question multiple times, but why would you want to do this? Can you give me a business scenario?