Looping in IICS Taskflows using Decision and Jump Steps

1. Introduction

Yes, it is possible to loop in Informatica Cloud taskflows by using Decision and Jump steps in conjunction. Though there is no dedicated step available to perform looping in IICS taskflows, we could make use of decision step to define a condition and loop it through Jump step until the defined condition is met.

To understand the process of building a taskflow which loops through a condition, let us take a problem statement and see how it can achieved by looping in IICS taskflows.

2. Problem Description

Consider a source table ‘Employee’ which contains employee details hired between 2018 and 2021.

The requirement is to load the employee details ‘Year wise’ into the target table. The initial run should load the employee records hired in 2018 into the target table. The second run should load the employee records hired in 2019 and so on until 2021.

The below image shows the year wise record count from the table Employee.

To achieve this we have built an IICS Mapping with Source as Employee table with filter condition as below.

to_char(hire_date,'yyyy') = '$$Param_Date'

Param_Date is an In-Out Parameter to which values could be passed from a parameter file or directly from a taskflow. The values expected for Param_Date are 2018, 2019, 2020 and 2021 in our requirement.

The below image shows the IICS mapping with source as ‘Employee’ table with filter condition applied to query the records based on year using the In-Out parameter ‘Param_Date’.

Create a Mapping Configuration Task for the mapping which will further be used in a taskflow.

3. Passing values to Mapping In-Out Parameter in IICS Taskflows

Before we implement the looping logic, let us first understand how to pass values to a Mapping In-Out parameter from IICS taskflow.

Follow below steps to pass values to a Mapping In-Out Parameter from IICS taskflow

1. Create a new taskflow and add taskflow name and details under General properties of the taskflow.

2. Add a Data Task step into the canvas.

3. Select the Mapping task with In-Out parameters in the Data Task step.

4. Under Temp Fields of Start properties, create a new field Year of type Text.

5. Add an Assignment step between Start and Data Task step.

6. In Assignment step, click on ‘+’ to add a field and select the field Year from the pop-up.

7. Enter the value of the year under From with Assigned Using as Content in the Assignment step.

8. Under Data Task step, go to Input Fields > Click on ‘+’ > select InOut Parameters > Parameter name (Param_Date in our example)

9. Under Value > click Edit > select Field from the drop down menu in the pop-up.

10. Select the temp field Year as the value for the Field as shown below.

11. Validate, Save and Publish the taskflow.

When you trigger the taskflow, the value which we are passing to Year through Assignment step is passed as In-Out parameter value in the mapping.

Now that we understood how to pass the values to In-Out parameters of a mapping in taskflow, let us discuss how to pass a range of values to In-Out parameters one after the other in a loop in IICS taskflows.

Related Article: Passing data from one task to another in IICS Taskflows

4. Looping between a range of values in IICS Taskflows

In this method, we define start and end values of the parameter and increment the start value until it reaches the end value. We verify this condition in every loop and pass the start value to Data task as In-Out parameter if the start value is less than end value. The start value will be incremented after successful completion of the Data task and the taskflow jumps back to decision step.

Follow below steps to pass a range of values to In-Out parameters in IICS taskflows.

1. Under Temp Fields of taskflow start properties, define two fields Start_Year and End_Year of type Integer.

2. Add an Assignment step after the Start and add values to the two temp fields created as 2018 and 2021 as shown below.

3. Add a Decision step after Assignment. Under Decision tab > Click To Select Field > select Start_Year.

4. Under Path1, select Condition as Greater than and Value as End_Year as shown below.

5. There will be two flows coming from Decision step.

• One flow where the condition we defined is met (Greater than End_Year).
• The other flow is where the condition is not met (Otherwise).

6. Since we want the taskflow to end once the condition we defined is met, under the Greater than End_Year flow add an End step.

7. Under the Otherwise flow add the Data Task step and select the Mapping task with In-Out parameters.

8. Under Input Fields of Data Task step, assign the field Start_Year as value for the In-Out parameter Param_Date as shown below.

9. Add an Assignment step after Data Task and increment the Start_Year by one as shown below.

10. Add a Jump step after the Assignment step. Select the Decision step from the drop down menu to jump.

The final taskflow design would be as below.

5. Looping through a List of values in IICS Taskflows

In this method we define a XML List with the required values to be passed to the In-Out parameter as list elements. Using a counter we read each element from the List using the counter value as Index. The element value will be passed to the Data Task as In-Out parameter after verifying that the counter value is less than element count of the List. After the successful completion of Data task, the counter will be incremented and jumped back to the fetch new value from the list. Once the counter reaches the element count of the list, the taskflow ends.

Follow below steps to pass a List of values to In-Out Parameters in IICS taskflows.

1. Under Temp Fields of taskflow start properties, define below fields.

2. Add an Assignment step after the Start. Assign values to the temp fields as shown below.

• Year_XML_List is a standard XML holding a list of values as a text.
• Year_Count fetches the count of all the elements in the List using the count function. The count function is used to count the total items present in a list.
• util:parseXML() is used to parse the text as a XML.

3. Add another Assignment step and assign value to the Year field as below.

Since the initial value of the Counter is 1, the output of the above expression will be 2018 which is first element in the List.

• Counter is used as an index to fetch the element from the List.
• /text() is used to fetch the element value without the XML tags.

4. Add a Decision step after the second Assignment step and define the condition as Counter Greater Than Year_Count as shown below.

5. There will be two flows coming from Decision step.

• One flow where the condition we defined is met (Greater than Year_Count).
• The other flow is where the condition is not met (Otherwise).

6. Since we want the taskflow to end once the condition we defined is met, under the Greater than Year_Count flow add an End step.

7. Under the Otherwise flow add the Data Task step and select the Mapping task with In-Out parameters.

8. Under Input Fields of Data Task step, assign the field Year as value for the In-Out parameter Param_Date as shown below.

9. Add an Assignment step after Data Task and increment the Counter by one as shown below.

10. Add a Jump step after the Assignment step. Select the second Assignment step where we assign the value for Year from the drop down menu to jump.

The final taskflow design would be as below.

6. Conclusion

In the both looping methods discussed in the article, the taskflow process the data from 2018 till 2021 in four different runs automatically as shown below.

This process is highly helpful when processing a huge amount of data in junks, be it any adhoc load or historical loads in your project. This acts as a Mini Automation which takes care of all your loads one by one without any manual intervention.

How do you process huge data in your project? Is there any better alternative you could think of to achieve the requirement discussed in the article? Let me know in the comments section.

Related Articles: