One often required functionality that's not inherent to any Informatica PowerCenter entity is to automate loop execution of arbitrary logic with exchange of input parameters. In this article I will give you the implementation steps so you can achieve this. Most common implementation occurs with execution of arbitrary Data Integration process for a time period that's comprised of multiple temporal scopes of a single process run. For example, reloading a full month of transactions after the cutoff date when retroactive bookings for previous month become impossible, by means of daily transaction increment.

Implementation steps are outlined in the following:

• Initialize the beginning and the end of time interval (usually a utility job targeted towards a control parameter file)

• Rolling out the time interval so we get a list of dates, each meant as input parameter for a single process run and again in parameter file, one date beneath the other

• Execute the necessary logic in a loop with changing input parameter values, by shell or batch script (depending on underlying OS):

• The script takes a date from the list created in point two

• Executes the necessary logic with appropriate parameter initialization

• Repeats until there are no more dates in the list

Pseudocode for the script facilitating the loop execution should be in lines with the following (first two steps are trivial and won't be contemplated at this time):

#####################################################################

1 # multiple executions of ETL process with altering parameter values

2

3 define a path to a flat file containing the ordered list of all the dates the ETL process is to be executed for

4

5 # begin

6

7 take first date from the top of the list from line [3] and continue until there are no more dates in the list

8

9 delete the control parameter file containing a single date meant for parameter initialization

10

11 spool the date from line [7] into a recreated control parameter file from line [9]

12

13 initialize the input parameters with the date from line [11] and execute the appurtenant ETL logic

14

15 # end

#####################################################################

Said logic can be easily implemented on both Microsoft Windows and all kinds of Linux/Unix distributions. This way, one can automate an often required functionality that is out of scope of inherent Informatica PowerCenter capabilities.

Hope this helps, if you have any tips or doubts, please leave a comment below.