If you are an Informatica PowerCenter user, you can use a PowerCenter mapplet to create a mapplet in Data Integration.
To use a PowerCenter mapplet, you create a mapplet in PowerCenter and export the mapplet to an XML file. Then you upload the XML file in to Data Integration.
A mapplet contains a set of transformations. A PowerCenter mapplet can contain one or more Source transformations but it cannot contain a Target transformation. You can use PowerCenter mapplets in the following Data Integration tasks:
•Synchronization tasks. You can use one mapplet in a synchronization task.
•Mapping tasks. You can use multiple mapplets in a mapping task. You can use mapplets in a Visio template or in a mapping. You cannot use a PowerCenter mapplet in advanced mode.
•Masking tasks. You can use passive mapplets in a masking task to mask target fields.
Active and passive PowerCenter mapplets
Mapplets are either active or passive.
When you upload a PowerCenter mapplet in Data Integration, you specify whether the mapplet is active or passive.
An active mapplet includes at least one active PowerCenter transformation. An active mapplet can return a number of rows different from the number of source rows passed to the mapplet. For example, an active mapplet might aggregate five source rows and return one row.
A passive mapplet includes only passive PowerCenter transformations. A passive mapplet returns the same number of rows that are passed from the source.
Stored Procedures in mapplets
When a PowerCenter mapplet that you want to use contains a Stored Procedure transformation, the stored procedure must include exception handling.
Exception handling can be as complex as necessary. Or, you can use the following simple example:
Exception when NO_DATA_FOUND then NULL; END;
For example, you have the following stored procedure in a PowerCenter workflow:
CREATE OR REPLACE PROCEDURE SP_GETSAL_WITH_EXCEPTION (EMP_ID NUMBER, EMP_NAME OUT VARCHAR, SAL OUT NUMBER) AS BEGIN SELECT EMPNAME INTO EMP_NAME FROM EMPLOYEE WHERE EMPID=EMP_ID; SELECT SALARY INTO SAL FROM EMPLOYEE WHERE EMPID=EMP_ID;
Before you export the workflow, add exception handling as follows:
CREATE OR REPLACE PROCEDURE SP_GETSAL_WITH_EXCEPTION (EMP_ID NUMBER, EMP_NAME OUT VARCHAR, SAL OUT NUMBER) AS BEGIN SELECT EMPNAME INTO EMP_NAME FROM EMPLOYEE WHERE EMPID=EMP_ID; SELECT SALARY INTO SAL FROM EMPLOYEE WHERE EMPID=EMP_ID; Exception when NO_DATA_FOUND then NULL; END;
PowerCenter XML files for mapplets
To use a mapplet in Data Integration, you upload a PowerCenter XML file that defines a PowerCenter mapplet.
Consider the following rules when you use a PowerCenter XML file to create a Data Integration mapplet:
•If the mapplet includes a transformation that uses a connection, then the PowerCenter XML file must contain only one workflow, one Session task, one mapping, and one mapplet.
If the mapplet doesn't include a transformation that uses a connection, then the PowerCenter XML file must include one mapplet. The workflow, Session task, and mapping are optional.
•The session can use any type of connection.
•You do not have to map all source and target fields in the PowerCenter mapping.
•The PowerCenter mapplet can contain the following supported transformations:
- Aggregator transformation
- Expression transformation
- Filter transformation
- HTTP transformation
- Lookup transformation
- Salesforce Lookup transformation (multiple matches returns a single match)
- Salesforce Picklist transformation
- Salesforce Merge transformation
- Sorter transformation
- Stored Procedure transformation with exception handling
- Transaction Control transformation
- Web Services consumer transformation
- XML Generator transformation with flat file or database sources
- XML Parser transformation with flat file or database sources
•If you use a mapplet in a synchronization task, the PowerCenter mapplet cannot contain multiple Input transformations.
•If you use a mapplet in a mapping task, the PowerCenter mapplet can contain multiple Input transformations.
•Data Integration flattens PowerCenter mapplets with multiple input groups into mapplets with one input group. Therefore, the ports in each input group in the PowerCenter mapplet must have unique names. If the names are not unique, rename the input ports in PowerCenter before you export the PowerCenter XML file that contains the mapplet.
•The PowerCenter mapplet cannot contain reusable objects such as shortcuts because Data Integration does not use a repository to store reusable objects. Export the mapplet without reusable objects.
PowerCenter mapplets in Data Integration tasks
Use the following rules and guidelines for using PowerCenter mapplets in Data Integration tasks:
•You can add Lookup transformations between the source and a mapplet.
•You can add Expression transformations between the source and a mapplet, and between a mapplet and the target.
•When you add a mapplet to a synchronization task, the synchronization task wizard removes existing field mappings. The synchronization task wizard doesn't remove existing field mappings if you add a passive mapplet between the source and target.
•When you use an active mapplet in a synchronization task that includes a saved query, the synchronization task ignores the configured target option for the task and inserts data to the target.
•Data Integration retains PowerCenter session-level overrides to the mapping.
Configuring a PowerCenter mapplet
To create a mapplet in Data Integration, you upload a PowerCenter XML file that contains a PowerCenter mapplet.
When you upload the PowerCenter XML file, Data Integration creates a mapplet based on the mapplet definition in the XML file.
1To create a mapplet, click New > Mapplets > Mapplet - PC Import and then click Create.
To edit a mapplet, on the Explore page, navigate to the mapplet. In the row that contains the mapplet, click Actions and select Edit.
2Configure the following details:
Detail
Description
Mapplet Name
Name of the mapplet.
Mapplet names can contain alphanumeric characters, spaces, and the following special characters: _ . + -
Maximum length is 100 characters. Mapplet names are not case sensitive.
Location
Project folder in which the mapplet is to reside.
Description
Description of the mapplet.
Maximum length is 255 characters.
Mapplet Type
Whether the mapplet is active or passive. The mapplet type depends on the type of PowerCenter transformations in the mapplet:
- Active. The mapplet includes one or more active PowerCenter transformations.
- Passive. The mapplet includes only passive PowerCenter transformations.
3To upload the PowerCenter XML file, click Upload.
4In the Upload Mapplet XML File dialog box, click Choose File.
5Browse to the appropriate location and select the PowerCenter XML file.
6Click OK.
The Mapplet XML File Details area shows the connections, input fields, and output fields.