In the time that I have been working with Informatica PowerCenter, I have learned a lot about this sometimes unruly product. PowerCenter seems to have a mind of its own when it performs unintended actions, or behaves in a way that we do not understand. The code behind the code that makes it easier to develop in Informatica also makes it harder to understand how to fix code that is not performing as needed.

Here are the top seven unusual facts that I wish I knew when I started my Informatica development career.

  1. A dynamic lookup will capture updates only if the output ports on the source table are connected to another transformation, even if the ports are not being used.
  2. If an XML over http web transformation call is unsuccessful, that pipeline dies on the spot if it is not first connected to a transformation other than a router. This is true even when other ports skip the http transformation.
  3. Just because the error returned is "Transformation Evaluation Error", it does not make it true. This error is often seen when there is another problem with a source or target table. Make sure to look through all errors before applying a fix.
  4. A parameter remains unchanged throughout the session run while a variable can be set to a new value at any point during, before, or after the run.
  5. Even if "persistent" variable is not checked, Informatica will hold on to any variable set in the mapping for the next run. This can either be overwritten by using a parameter file or by using a pre-session assignment of a NULL value to the variable.
  6. If a zero ends up in a target number field when another number was expected, there is probably a varchar port somewhere upstream.
  7. If the mapping creates a dynamic file name, a dummy file must also be named at the workflow level because the workflow does not know the name of the file until you pass it over, which will cause a fatal error.

Hopefully, these tips will save you some headaches and search engine time.