1 Reply Latest reply on Feb 21, 2019 9:10 AM by Nico Heinze

    The source table structure will be changed on-the-fly / dynamically in PowerCenter

    Saravana Kumar New Member

      My requirement is, The source table structure will be changed on-the-fly / dynamically in Informatica PowerCenter

      For example,

      Let's consider the source and target is a flat file. In a mapping, right now there are 10 columns in source definition, those columns are mapped to respective target columns. In the next couple of days, newly 4 more columns would be added in the source. Now while running this respective session, the source/target definition should be changed on-the-fly with new columns and loaded the data into the target table.  How we can achieve this.

      No manual work here for adding the columns in mapping design level.

        • 1. Re: The source table structure will be changed on-the-fly / dynamically in PowerCenter
          Nico Heinze Guru

          Now that's a pretty tough thing to do.

          You have to start by getting informed that a source structure changes.

          Now whenever a source structure changes, you have to adapt the respective source definition within the repository.

          Next you have to automatically adapt your mapping structure to those changes.

          Not to forget all dependent workflows.

          And finally you can re-run those workflows to work with the new structure.

           

          That was the highest-level abstract of what you need to implement.

          The only halfway reasonable approach which I see is to build some kind of "meta mapping utility" based on the Design API provided by Informatica for PowerCenter (part of the so-called Informatica Developer Platform, IDP, which is the official API collection for PowerCenter). This will be quite some work; you need a metadata repository for that Java utility (the Design API is Java based), you need to programmatically recreate the affected source and target definitions and mapping and sessions and workflows; you need to automatically import those changed workflows into the PowerCenter repository (which is pretty easy, compared with all the other stuff you have to do); and last not least you'll have to work with the command-line utility "pmrep" to re-validate all the affected workflows and to make them executable again.

           

          Have fun. Sounds like a fun job for me, but then I'm an old-fashioned C hacker who loves hacking bytes and bits.

           

          Regards,

          Nico