Tasks > PowerCenter tasks > PowerCenter workflows

PowerCenter workflows

To use a PowerCenter workflow for a PowerCenter task, the workflow objects must be objects that the Data Integration PowerCenter task supports.

Consider the following rules when you use a PowerCenter workflow for a PowerCenter task:

•The PowerCenter XML file must only contain one workflow.
•The workflow must contain one Session task with one mapping.
•The workflow cannot include task types other than Session tasks.
•Do not edit the XML file after you've exported the workflow from PowerCenter. Instead, change the workflow in PowerCenter and then export it again.
•The session can contain up to 64 partitions for sources and targets.
•The session can use pre-session and post-session commands.
•The session must have the Enable High Precision session property enabled.
•The mapping must contain a source definition and target definition.
•The mapping cannot contain an IIF expression with values of different data types, such as the following IIF expressions:

[IIF(ANNUALREVENUE >0,NAME)]
[IIF(emplid_offset = 'Y',LINE_NO + 1,LINE_NO]

•The mapping cannot include reusable objects such as reusable transformations or shortcuts because Data Integration doesn't use a repository like PowerCenter does, so reusable objects cannot be stored.

Supported transformations and mapping objects

The mapping objects in a workflow must be supported by Data Integration.

A mapping can include the following source and target types:

•Flat file
•FTP/SFTP
•Database
•Salesforce
•SAP
•Web service

A mapping can include the following transformations:

•Aggregator transformation
•Data Masking transformation
•Expression transformation
•Filter transformation
•HTTP transformation
•Java transformation
•Joiner transformation
•Lookup transformation
•Normalizer transformation
•Router transformation
•Salesforce Lookup transformation
•Salesforce Picklist transformation
•Salesforce Merge transformation
•SAP IDOC Interpreter transformation
•SAP IDOC Prepare transformation
•Sequence Generator transformation
•Sorter transformation
•Stored Procedure transformation
•Transaction Control transformation
•Union transformation
•Update Strategy transformation
•Web Services Consumer transformation
•XML Parser transformation with file or database sources
•XML Generator transformation with file or database sources

If the workflow contains transformations or mapping objects other than the objects listed above, the workflow upload to Data Integration might fail.

Exception handling in stored procedures

When a mapping that you want to use in a PowerCenter task 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:

Pre-session and post-session commands

You can use pre-session and post-session SQL or shell commands in a workflow that you want to use in a PowerCenter task.

You might use a pre-session or post-session command to start FTP/SFTP scripts or stored procedures, rename or archive files, or run post-processing commands. Configure pre-session and post-session commands in the PowerCenter session.

When you configure a pre-session or post-session command, you can enter a single command or you can call a batch file that contains a set of commands. If you use a batch file, be sure to use complete paths or directories. When you configure the pre-session or post-session command in PowerCenter, enter the complete path or directory along with the file name, such as c:/IC PowerCenter Task Commands/pre-session1.bat.