Managing constraints – the target load plan

While you work on multiple mappings in a complex scenario, situations might demand that you put multiple data flows in a single mapping in order to justify the performance and the complexity. A sample mapping depicting the preceding statement is shown in the following screenshot:

If you execute the mapping, the session might fail because of the primary key and foreign key violation. As the Employee and Department tables have dependencies on each other, when you run the mapping containing both the flows, the process might fail if the data violates the dependency.

To avoid the issue, Informatica PowerCenter contains a utility called the target load plan—how do you plan to load the data into multiple targets in a mapping? We can load the data in a particular sequence in multiple targets in a mapping in order to avoid failure due to constraints.

Consider that the EMPLOYEES table data depends on the DEPARTEMNTS data because of the primary key and foreign key constraints. So, to satisfy the referential integrity, the DEPARTMENTS table should be loaded first. The target load order is useful when you wish to handle referential integrity while inserting, deleting, or updating data in tables that have the primary key and foreign key relationships.

To set the target load plan in a mapping, perform the following process:

To set the target load plan, just move the target load group using the arrow provided in the window. To load the DEPARTMENTS table before the EMPLOYEES table, select SQ_DEPARTMENTS and click on the up arrow, as shown in the preceding screenshot.

This will make the DEPARTMENTS table load before the EMPLOYEES table is loaded, and we can avoid failure.

When we use the target load plan, Informatica PowerCenter actually restricts the extraction of the data from the source qualifier. It waits for the first flow in the selected sequence to finish loading the data and then starts extracting the data from the second source qualifier for its loading.