Design > Integration Templates > Step 1. Configure an Integration Template

Step 1. Configure an Integration Template

Configure an integration template in the Cloud Integration Template Designer to define flexible, reusable data flow logic for use in Mapping Configuration tasks.

An integration template includes at least one source definition, source qualifier, target definition, and links that define how data moves between objects. An integration template can include other Informatica objects, such as a Lookup object to define lookups or a Joiner object to join heterogeneous sources.

A pipeline consists of a source qualifier and all the objects and targets that receive data from the source qualifier. You can include one or more pipelines in an integration template.

When you configure an integration template, you can configure template parameters. Template parameters are values that you can define in Mapping Configuration tasks.

In an integration template, sources, targets, and lookups are always template parameters. You can create additional template parameters for other data flow logic, such as filter or join conditions or other expressions.

You can use expression macros in Expression and Aggregator objects. Expression macros allow you to define dynamic expression logic.

You can also use user-defined parameters in integration templates and Mapping Configuration tasks. User-defined parameters are values that you define in a parameter file. You can update user-defined parameter values without editing the integration template or the Mapping Configuration task. You associate a parameter file with a Mapping Configuration task.

When you configure an integration template, you can configure the following:

•Source and target objects to be used in the task.
•Data flow logic. To perform data transformation add the appropriate objects, such as an Expression object to configure expressions.
•Links and rules. Create links between objects and link rules to define how data moves between objects.
•Template parameters. Use template parameters for values that you want to define later in the process. Sources, targets, and lookups are always template parameters. Create additional template parameters as necessary.
•Expression macros. (Optional.) If your data flow includes Expression or Aggregator objects, you can use expression macros to enable flexible expression logic.
•User-defined parameters. Configure user-defined parameters for values that you define outside of the integration template or Mapping Configuration task.

Creating an Integration Template

Though you use the Cloud Integration Template Designer to configure integration templates, you can use several methods to create the initial integration template XML file.

The following table describes when and how to use each method. Use the method that best suits your requirements.

Integration Template Creation Method	Description
Create the integration template in the Cloud Integration Template Designer.	Use to create data flow logic entirely in the Cloud Integration Template Designer.
Export a mapping from PowerCenter.	Use when you have a PowerCenter mapping that you want to parameterize and use in Informatica Cloud. To create an integration template from an existing PowerCenter mapping: - In the PowerCenter Designer, export the mapping to XML. - In the Cloud Integration Template Designer, use the Create Template from Mapping XML button in the Informatica toolbar. - Configure the template in the Cloud Integration Template Designer.
Export a task from Informatica Cloud.	Use when you have an Informatica Cloud Data Synchronization or Mapping Configuration task that you want to expand and parameterize. To create an integration template from an existing Informatica Cloud task: - To use an existing Data Synchronization task, on Data Synchronization Tasks page, click the Download Mapping XML icon for the task you want to use. To use an existing Mapping Configuration task, on Mapping Configuration Tasks page, click the Download Workflow XML icon for the task you want to use. - In the Cloud Integration Template Designer, use the Create Template from Mapping XML button in the Informatica toolbar. - Configure the template in the Cloud Integration Template Designer.

Integration Template Information in Tasks

When you configure an integration template, you can add information that displays when an Informatica Cloud user imports an integration template or creates a Mapping Configuration task based on the template. This information can guide the user on how to work with the integration template.

You can add or update this information in Informatica Cloud when you import or edit an integration template.

The following table describes how information in the integration template displays in the Mapping Configuration task:

Integration Template Element in the Cloud Integration Template Designer	Update in Informatica Cloud	Display in the Mapping Configuration Task Wizard
Image of the integration template data flow displays object and link names. Descriptive link names can help explain data flow logic.	Upload the image file on the New Integration Template or Edit Integration Template page.	Definition page.
Source object name.	Label property on the New Integration Template or Edit Integration Template page.	Source connection and object on the Sources page.
Target object name.	Label property on the New Integration Template or Edit Integration Template page.	Target connection and object on the Targets page.
Template parameter name.	Label property on the New Integration Template or Edit Integration Template page.	Template parameter label.
Template parameter description in the Show Parameters dialog box.	Template parameter description on the New Integration Template or Edit Integration Template page.	Template parameter tooltip.

Template Parameters

A template parameter is a placeholder for a value or values in an integration template. You configure template parameters when you configure Informatica objects in the integration template data flow.

You can define the value of the template parameter when you import the integration template to your Informatica Cloud organization or when you create a Mapping Configuration task based on the integration template.

You can create a template parameter for any logical aspect of a data flow. Sources, targets, and lookups are always template parameters. You can create additional template parameters for other aspects of the data flow logic. Some template parameters you might want to create include the following:

•Source filter in a source qualifier
•Filter condition in a Filter object
•Join condition in a Joiner object
•Update strategy condition in an Update Strategy object
•Expression in an Expression object
•Expression in an Aggregator object
•Link rule for a link

For example, if you have regional lookup data in different lookup tables, you might create a $lookuptable$ template parameter in the Lookup object that represents the lookup table. When you configure the Mapping Configuration task, you select the lookup connection and table that you want to use. You configure a different Mapping Configuration task for each regional lookup table.

To create a template parameter in an integration template, surround the template parameter name with dollar signs as follows: $<template_parameter_name>$. Template parameter names are case sensitive.

By default, the template parameter name displays as the template parameter label in the Mapping Configuration Task wizard. However, you can also configure a template parameter label in the template or after you import the template.

You can use the Show Parameters button on the Informatica toolbar to see the template parameters defined using the $<template_parameter_name>$ syntax. If you do not use the template parameter name syntax to configure source, target, or lookup template parameters, they do not display in the Show Parameters dialog box.

Template Parameter Usage

Template parameters are flexible placeholders that you can use in many different ways. Some examples:

•Output field. You can use a template parameter to define a field.

In the following example, $fullname$ is an output field that merges first and last name data from the source:

$fullname$ = CONCAT(fname,lname)

•Expression clause. You can use a template parameter as part of a larger expression.

For example, you might use the following in a WHERE clause:

fullname=$nameexpr$

Or, in the following example, the unit price template parameter and quantity template parameter define the TOTAL output field.

TOTAL=$unitpricefield$*$quantityfield$

You might create a template parameter for a filter condition as follows:

$filter$

Or, use a template parameter to represent a field in a filter condition:

NOT ISNULL($id$)

You might create a lookup condition using two template parameters as follows:

$lookupID$=$sourceID$

You could create use a template parameter in a link rule as follows:

$custid$ (to) CUST_ID

•Expressions. If you configure a template parameter to use the field mapping input control, you can configure an entire expression or sets of expressions. With the field mapping input control, you can use any input field in expressions.

Expression Macros in Integration Templates

Expression macros are macros that can help create flexible expressions in integration templates. You can use expression macros in Expression and Aggregator objects.

Use an expression macro to specify repetitive expression statements or complex expressions. Expression macros apply a common expression pattern across a set of fields, such as adding all or a set of fields together, checking if fields contain null values, or converting dates to a different format.

You can use an expression macros to generate output fields and variable fields.

An expression macro consists of several parts:

•Macro variable declaration. When you declare the macro variable, you declare the macro variable name and the fields to be used in the macro expression. Use the link rule format to declare the fields. As with link rules, you can list actual field names or use a link rule, such as All Ports or Pattern.

Use the following syntax for the macro variable declaration:

Macro Variable Declaration Element	Description	Syntax
Macro variable name	Macro variable name. Use a logical name.	Declare_%<macro variable name>%
Macro variable fields	Fields to be used in the macro expression. You can list field names, create an expression to define the field names, or use a link rule. For example, you can use the following syntax to apply a macro across all fields in the object: {"port":"All Ports"} When listing field names, use a comma delimited list. You can create more than one set of variable fields as follows: {"<variable1>"}:"value1, value2..."}, "<variable2>":"value1, value2..."} When you use a variable in an expression, enclose the variable name in percent signs, as follows: %<macro variable field name>%	{"<macro variable field name>":"<macro variable field list, expression, or rule>" }

•Macro statement. When you define the macro statement, you define the names of the macro output field names and the macro expression.

Macro Statement Element	Description
Output field names	Expression that defines the field names for the output fields generated by the expression macro. You can use variables and rules to help define output field names, such as <%field%>_out.
Macro expression	Expression to be used.

For example, you might use the following expression macro to check if any of the address fields that start with "addr" are null. The ISNULL output port will be set to a value 1 or higher if one or more fields are null:

Macro variable name: Declare_%addressports%
Macro variable fields: {"addrport":"Pattern:^addr"}
Output field names: ISNULL
Macro expression: %OPR_SUM[IIF(ISNULL(%addrport%),1,0)]%

Template Parameters

You can use template parameters in expression macros.

For example, you might use a template parameter in the following macro variable declaration to define the fields to be used in the expression macro:

Macro variable name: Declare_%input%
Macro variable fields: {"inputfields":"$salesdata$"}

When you configure the Mapping Configuration task, $salesdata$ displays as a template parameter. The fields that you define for the template parameter are expanded where you use the %inputfields% variable in the Expression object.

Patterns in Expression Macros

You can use Mapping Architect for Visio patterns in expression macros.

For example, if you know that you want to use all fields that begin with SALES_, you might declare the macro variable fields as follows:

Macro variable name: Declare_%salesfields%
Macro variable fields: {"SalesFields":"Pattern:^SALES_"}

Or, if you know that you want to use all input fields, you might use the following expression:

Macro variable name: Declare_%salesfields%
Macro variable fields: {"SalesFields":"All Ports"}

For more information about patterns, see the Mapping Architect for Visio documentation.

Horizontal and Vertical Expansion in Integration Templates

An expression macro can expand vertically or horizontally. You can use both horizontal and vertical expansion in an expression macro.

A vertical expansion performs the same calculation on multiple fields by generating multiple expressions. To use a vertical expansion, configure a macro input field that represents multiple incoming fields. When the task runs, the application performs the same calculations on each field that the macro input field represents.

For example, the following expression trims leading and trailing spaces from the address ports defined by the %addr% variable and writes the results to output fields with a suffix of _o:

%addr%_o = LTRIM(RTRIM(%addr%))

This results in the following expressions in the following output fields:

address1_o = LTRIM(RTRIM(address1))
address2_o = LTRIM(RTRIM(address2))
city_o = LTRIM(RTRIM(city))
state_o = LTRIM(RTRIM(state))
zipcode_o = LTRIM(RTRIM(zipcode))

Horizontal expansion performs a calculation across multiple fields while expanding a single expression. To use a horizontal expansion, configure a macro input field that represents a set of incoming fields or a set of constants. When the task runs, the application expands the macro input field, and then uses the fields or constants to calculate a complex expression.

You can use the following horizontal expansion functions:

%OPR_CONCAT%: Uses the CONCAT function and expands an expression in an expression macro to concatenate multiple fields. %OPR_CONCAT% creates calculations similar to the following expression:
%OPR_CONCATDELIM%: Uses the CONCAT function and expands an expression in an expression macro to concatenate multiple fields, and adds a comma delimiter. %OPR_CONCATDELIM% creates calculations similar to the following expression:
%OPR_IIF%: Uses the IIF function and expands an expression in an expression macro to evaluate a set of IIF statements. %OPR_IFF% creates calculations similar to the following expression:
%OPR_SUM%: Uses the SUM function and expands an expression in an expression macro to return the sum of all fields. %OPR_SUM% creates calculations similar to the following expression:

For example, the following expression checks if any of the fields are null. If a field is null, it sets the Isnull field to a positive number:

Isnull=%OPR_SUM{IIF(ISNULL(%fields%),1,0]%

When expanded, the expression macro generates the following expression, and expands the expression to include all fields defined by the %fields% variable.

Isnull=IIF(ISNULL (fieldA, 1,0) + IIF(ISNULL(fieldB, 1, 0)...

Expression Macro Configuration

Configure an expression macro on the Configuration tab of the Expression or Aggregator object properties dialog box.

When you configure an expression macro, use one row for the macro variable declaration and another for the macro statement. Enter expression macro elements in the Port Name and Expression columns as follows. Data type and port type information is not relevant:

Expression Macro Part	Port Name	Expression
Macro variable declaration	Macro variable name. For example: Declare_%addressports%	Macro variable fields. For example: {"addrport":"Pattern:^addr"}
Macro statement	Output field names. For example: ISNULL	Macro expression. %OPR_SUM[IIF(ISNULL(%addrport%),1,0)]%

Parameter Files and User-Defined Parameters

A parameter file is a list of user-defined parameters and their associated values. You can use user-defined parameters in integration templates and Mapping Configuration tasks.

Use a parameter file to define values that you want to update without having to edit the integration template or the Mapping Configuration task. For example, you might use a user-defined parameter for a sales quota that changes quarterly. Or, you might configure a task to update user-defined parameter values in the parameter file at the end of the job, so the next time the job runs, it uses the new values.

You can include user-defined parameters for multiple integration templates or Mapping Configuration tasks in a single parameter file. You can also use multiple parameter files for different integration templates or tasks. The Mapping Configuration task reads the parameter file before a task runs to determine the start values for the user-defined parameters used in the task.

User-defined parameter values are treated as String values. When you use a user-defined parameter in an expression, use the appropriate function to convert the value to the necessary datatype. For example, you might use the following expression to define a quarterly bonus for employees:

IIF((EMP_SALES < TO_INTEGER($$SalesQuota), 200, 0)

To use a parameter file, perform the following steps:

1. Use a user-defined parameter in an integration template or Mapping Configuration task.

- Use two dollar signs to name the parameter, as follows: $$<user-defined_parameter>.
- When you use the user-defined parameter in an expression, convert the String parameter value to the appropriate datatype as necessary.
- If you use a user-defined parameter in a filter, start the filter with the user-defined parameter.

2. Use the following format for the parameter file:

[Global]
$$<user-defined_parameter>=value
$$<user-defined_parameter2>=value2

For example:

[Global]
$$SalesQuota=1000
$$Region=NW

Note: The value of a user-defined parameter includes any characters after the equals sign (=), including leading or trailing spaces. User-defined parameter names are case-sensitive.

You can save the file as several different formats, such as *.txt, *.doc, or *.param.

3. Save the parameter file to a directory local to the Secure Agent to run the task.

Use the following directory:

<Secure Agent installation directory>/apps/Data_Integration_Server/data/userparameters

4. Enter the parameter file name on the Schedule page of the Mapping Configuration Task wizard.

Object-Level Session Properties

Object-level session properties are advanced properties based on PowerCenter session properties for transformation objects. You can configure object-level session properties for source qualifier and target objects.

For source qualifier objects, you can configure object-level session properties such as a SQL query override or pipeline partitioning attributes. Target objects allow different object-level session properties based on target type, such as null characters or delimiters for flat file targets or target load type for database targets.

Configure object-level session properties in the Session Properties field on the Properties tab of a source qualfier or target object. Use XML to configure the session properties that you want to use. Use the following syntax:

<attribute name="<session property name>" value="<value>"/>

For example, you can use the following XML to define target properties in a target object:

To define partition properties, use a slightly different format. For example, to define read partitions for a database table, you could enter the following XML in the the source qualifier object Session Properties field:

Note: XML is case sensitive. Also, unlike in PowerCenter, use an underscore for the KEY_RANGE option.

Visit the Informatica Cloud Community for additional details and examples. You can browse or search for "session properties".

Optional Objects

You can configure objects in an integration template data flow as optional. When data is not passed to an optional object in a Mapping Configuration task, the object is not included in the final data flow for the task.

You can configure any object as optional except source or source qualifier objects.

For example, you might have an optional Expression object that updates the date format of date data routed from the source. The updated data is routed back to the main data flow. If the source selected for the task does not include date data or a date field that is routed to the Expression object, Informatica Cloud omits the optional Expression object from the final data flow used for the task.

When you use an optional object, make sure the data flow is still valid if the object is not included. If the data flow is not valid without the optional object, errors can occur when the task runs.

To configure an object as optional, on the Properties page of the object details dialog box, set the Optional property to True.

Rules and Guidelines for Configuring an Integration Template

Use the following general rules and guidelines for configuring an integration template:

•An integration template must include the following objects:

- Source and target objects to be used in the task.
- Data flow logic. To perform data transformation add the appropriate objects, such as an Expression object to configure expressions.
- Links and rules. Create links between objects and link rules to define how data moves between objects.
- Template parameters. Use template parameters for values that you want to define later in the process. Sources, targets, and lookups are always template parameters. Create additional template parameters as necessary.
- Expression macros. (Optional.) If your data flow includes Expression or Aggregator objects, you can use expression macros to enable flexible expression logic.
- User-defined parameters. Configure user-defined parameters for values that you define outside of the integration template or Mapping Configuration task.

•Use the Source Definition object for all source types.
•For database and flat file sources, use the Source Qualifier object. For all other source types, use the Application Source Qualifier object.
•You can use any target object to represent different target types.
•You can include one or more pipelines in an integration template.
• The following objects are not supported at this time:

- Unconnected Salesforce lookups
- Unconnected stored procedure
- Related Salesforce sources

•When naming a link, use a name that describes the rules associated with the link. This allows you to understand a data flow without having to read the rules for each link.
•Do not create a Mapplet object in the Cloud Integration Template Designer. You can use a Mapplet object in an integration template if you can create the mapplet as part of a PowerCenter task and export the PowerCenter mapping XML to be used as an integration template.
•When you configure a data flow that contains expression macros and template parameters to use the field mapping input control, keep them in different Expression objects. Use one Expression object for the expression macros, and another for the field mapping template parameters.
•Use the Informatica Cloud transformation language to define expressions.
•For a Normalizer object, generated key values are reset with each job. For a Sequence Generator object, generated key sequence values are also reset with each job.
•When you configure multiple group by ports for an Aggregator object, use a semicolon to separate field names. If you create a template parameter for group by ports, you might add a template parameter description when you import the integration template to pass this information to the task developer.
•You can use connected stored procedures and stored functions in a mapplet or in the data flow. Use the following guidelines when using stored procedure objects:

- Define the stored procedure in the Expression Text property by stating the stored procedure name, input fields, and output fields:

<Stored Procedure Name>(<inputfield1 datatype>(<precision>,<scale>) IN <inputfield1>, <inputfield2 datatype>(<precision>,<scale>) IN <inputfield2>... <outputfield1 datatype>(<precision>,<scale>) OUT <outputfield1>, <outputfield1 datatype>(<precision>,<scale>) OUT <outputfield2>...)

- Match the order of the fields with the order of the template parameters defined in the stored procedure.
- Connect all fields in a stored procedure object.
- When a stored procedure is used in the data flow, you can parameterize the expression text to reuse same template with different stored procedures. In this case, the input and output link rules for the stored procedure object also needs to be parameterized and link rules mapping should maintain the order of the stored procedure template parameters.
- To use a stored function, enter the following syntax in the Expression Text property:

<return value datatype>(<precision>,<scale>)<Stored Function Name>(<inputfield1 datatype>(<precision>,<scale>) IN <inputfield1>, <inputfield2 datatype>(<precision>,<scale>) IN <inputfield2>... <outputfield1 datatype>(<precision>,<scale>) OUT <outputfield1>, <outputfield1 datatype>(<precision>,<scale>) OUT <outputfield2>...)

- If issues with string datatypes occur, use the NSTRING datatype for string values.

•The field mapping input control appends "_OUT" to the field mapping output field names. When you plan to use a field mapping input control for a template parameter, use a link rule to move output fields with the _OUT suffix to the next object in the data flow.
•When you plan to use a field mapping input control to write data to multiple targets with matching field names and matching datatypes, precision, and scale, connecting one field in one target results in writing data to matching fields in both targets. If the target fields have the same name, but different datatypes, precision, or scale, you can map one of the target fields.
•Avoid using Mapplet objects or multiple targets to a data flow after a field mapping input control.
•When you use multiple instances of the same source object, use a unique table name for each instance of the source. Similarly, when you use more than one instance of a target object, use a unique table name for each instance of the target. And when you use more than one instance of a lookup, use a unique table name for each instance of the lookup.
•You can use the %ALL% expression macro in an expression to represent all field names in a transformation. For more information, see "Using the %ALL% Keyword in an Expression" in the Informatica PowerCenter Mapping Architect for Visio Guide.
•Use an Update Strategy object only when the target table has keys defined.
•In Custom Transformation objects, do not configure a field as both an input and output field and do not include all input fields in the OUTPUT group.
•To include the fields from a Custom Transformation in field mappings, set the Fixed Ports property to YES.

Tips for Working with the Cloud Integration Template Designer

Use the following tips for working with the Cloud Integration Template Designer:

•Before creating a template, make sure you install the Cloud Integration Template Designer.
•Configure integration templates in the Cloud Integration Template Designer. Use the Informatica toolbar and Informatica stencil to configure the template.
•Enable macros in Microsoft Visio to enable full functionality for the Cloud Integration Template Designer.
•For Mapping Architect for Visio 2010, the Informatica toolbar displays on the Add-Ins tab.
•Use the Show Parameters icon on the Informatica toolbar to see the template parameters declared in an integration templates that use the $<template_parameter_name>$ syntax.
•Use the Validate Mapping Template icon on the Informatica toolbar to perform basic validation.
•You can cut and paste objects within an integration template. However, you might encounter errors if you try to copy and paste across different integration templates.
•When you configure a link, Mapping Architect for Visio indicates that the link is connected to an object by highlighting the object. Connect both sides of the link.
•The Mapping Architect for Visio Dictionary link rule is not supported at this time.
•For general information about using Mapping Architect for Visio, see the Mapping Architect for Visio documentation. The Mapping Architect for Visio documentation is available in the Informatica Cloud Developer Community: https://network.informatica.com/docs/DOC-15318.

The following Mapping Architect for Visio functionality is not relevant to creating integration templates for Informatica Cloud:

•The Declare Mapping Parameters and Variables icon on the Informatica toolbar.
•The <template_name>_<param>.xml file created when you publish a template. Do not use this file.

Template Parameter Rules and Guidelines

Use the following rules and guidelines when configuring template parameters in a template:

•Enclose template parameters in dollar signs as follows: $<template_parameter_name>$.
•Source and target objects are always template parameters, even when the names are not enclosed in dollar signs. Lookup objects are also template parameters.
•To avoid confusion when defining values for a template parameter, use a logical name for each template parameter and use a unique name for each template parameter in a template.
•When you parameterize a user-defined join, use the fully-qualified name in the parameter value.
•You can use the Show Parameters icon on the Informatica toolbar to view all template parameters in the file.
•Template parameter names and values are case-sensitive unless otherwise noted.

Tips for PowerCenter Mapping XML Templates

Use the following tips for integration templates created from PowerCenter mapping XML files:

•Rename sources, targets, lookups, and link rules in the integration template to provide meaningful names. Sources, targets, and lookups become template parameters automatically in an imported integration template.
•You can include the following PowerCenter objects in an integration template:

- Aggregator
- Application Source Qualifier
- BAPI/RFC transformation
- Expression
- Joiner
- Lookup
- Mapplets
- Normalizer
- Rank
- Router
- SAP/ALE IDoc Prepare
- Sequence Generator (connected only)
- Source
- Source Qualifier
- Sorter
- Stored Procedure (connected only)
- Target
- Transaction Control
- Union
- Update Strategy
- Web Services transformation
- XML transformation

•You can use the following PowerCenter objects in an integration template only when included in a mapplet:

- HTTP transformation
- Java transformation
- SQL transformation
- Unstructured Data transformation

•Do not create a Mapplet object in the Cloud Integration Template Designer. You can use a Mapplet object in an integration template if you can create the mapplet as part of a PowerCenter task and export the PowerCenter mapping XML to be used as an integration template.
•Do not rename mapplets in integration templates. Keep mapplet names as exported from PowerCenter.
•Do not use the same mapplet more than once in an integration template.
•Use only one connection type in a mapplet.
•Do not use PowerCenter mapping parameters and variables in integration templates. PowerCenter mapping parameters and variables are not supported.
•Do not use the PowerCenter $Source or $Target variables in mapplets.
•When joining two sources with a single source qualifier, configure a user-defined join in the source qualifier. Query overrides are not supported.

Tips for Informatica Cloud Workflow XML Templates

Use the following tips for integration templates created from Informatica Cloud workflow XML files:

•Rename sources, targets, lookups, and link rules in the integration template to provide meaningful names. Sources, targets, and lookups become template parameters automatically in an imported integration template.