HOW TO: Parameterize Source and Target Connections in Informatica Cloud (IICS)?

1. Introduction

The Connections in Informatica Cloud Data Integration can be parameterized at the mapping level by creating Input parameters. But the values to the parameters defined for connections needs to be passed at the Mapping Task level.

However there is an option to override the connection defined at the Mapping Task level with values specified in a parameter file at runtime.

Let us understand how to override connections defined at Mapping Task level using parameter file using a simple example.

2. Overriding Connections using Parameter file in Informatica Cloud

Consider below mapping which reads data from Oracle HR.EMPLOYEES table. The source uses a connection named “Oracle_HR”.

Steps to Override Connections using Parameter file

1. Parameterize the Connection by clicking on the New Parameter.

• Enter the name for the connection parameter as “Src_Connection”.
• Select the Connection Type. Selected Oracle as part of this example.
• Select Allow parameter to be overridden at runtime option. This option lets the value defined at mapping task level to be overridden by value defined in parameter file.

NOTE: Since we are directly creating the parameter from Source transformation, the parameter type is by default selected as connection. If you are creating the parameter from parameters panel, you need to select the Type as connection manually.

Once the connection is parameterized, you cannot select a different source object or make changes to field’s metadata. Select the actual connection back in order to make any such changes.

2. In the mapping task, define the parameter details.

• Select the Connection Name from the drop down.

• On the Schedule tab, enter the parameter file directory and file name.

3. In the parameter file, define the connection parameter with the value that you want to use at runtime. (The value must be a valid connection that is already created in the IICS environment)

Precede the parameter name with two dollar signs ($$). In our example, we have a parameter with the name Src_Connection and to override it with the connection “Oracle_SCOTT”, define the parameter file in the following format.

#USE_SECTIONS
[Default].[MCT_Employee_Details]
$$Src_Connection=Oracle_SCOTT
[Global]

Verify the task run details from Monitor

The below log shows that the task completed successfully and it picked the connection value defined in the parameter file.

The query from the session log also shows that EMPLOYEES table from SCOTT schema is picked as expected instead from HR schema.

SELECT SCOTT.EMPLOYEES.EMPLOYEE_ID, SCOTT.EMPLOYEES.FIRST_NAME, SCOTT.EMPLOYEES.LAST_NAME, SCOTT.EMPLOYEES.EMAIL, SCOTT.EMPLOYEES.PHONE_NUMBER, SCOTT.EMPLOYEES.HIRE_DATE, SCOTT.EMPLOYEES.JOB_ID, SCOTT.EMPLOYEES.SALARY, SCOTT.EMPLOYEES.COMMISSION_PCT, SCOTT.EMPLOYEES.MANAGER_ID, SCOTT.EMPLOYEES.DEPARTMENT_ID FROM SCOTT.EMPLOYEES

3. How to handle the Source and Target connections during the migration?

There are two different scenarios that arise with Connections while migrating the code from one environment to other in Informatica Cloud.

Scenario-1: When same connection names are used across all environments

When we migrate the code from one environment to other, by default the connection details also gets migrated. For example, if you are migrating the code from DEV to QA, the connection associated to those mappings gets created in QA if it is not existing already.

However, the password details are not migrated and needs to be entered manually in QA (You can change the other connection details like Host Name etc., according to QA environment).

If the connection is already existing with same name as DEV connection in QA and is configured already according to QA environment, the mappings will use that connection and there is no need to change connection details.

Scenario-2: When different connection names are used across environments

If you are using different connection names across environments like Oracle_DEV in DEV and Oracle_QA in QA environments etc., there are two different ways it could be handled.

While migrating the code into QA, change the connection name.

• The connection Oracle_QA should already be created in QA environment.
• In the Import wizard, you can change the connection from Oracle_DEV to Oracle_QA under Review Connections tab while migrating.

• This will be reflected in all the mappings which are using Oracle_DEV connection and the connection name is modified to Oracle_QA once code is imported.

While migrating the code into QA, import the mappings without any connection changes.

• This creates a new connection Oracle_DEV in QA environment.
• Create a connection Oracle_QA which connects to QA environment.
• Using the parameter file override the connection value with Oracle_QA. 

But there is an important step that needs to be handled.

• When a new connection Oracle_DEV gets created, the connection is by default INVALID as the password details are not imported.
• Hence the Mapping Task becomes INVALID and you cannot override the connection using parameter file as the task fails at initial step.
• To overcome the issue, enter the valid DEV credentials in the connection and make it VALID. Hence the mapping task with DEV connection will become VALID and can be overridden with QA connection using parameter file.

The disadvantage of this method is that you end up having both QA and DEV connections in same environment which is not ideal. But anyone with access to mappings will only be able to view DEV connection which will be overridden with QA connection during run time.

4. Overriding Data Objects using Parameter file in Informatica Cloud

Additionally the data objects in Informatica Cloud can also be parameterized just like connections and can be overridden with values specified in a parameter file during runtime.

Steps to Override Data Objects using Parameter file

1. Parameterize the Data object by creating a New Parameter.

• Enter the parameter name.
• Select the Type as data object.
• Select Allow parameter to be overridden at runtime option. This option lets the value defined at mapping task level to be overridden by value defined in parameter file.

2. In the mapping, use the data object parameter where you want to override.

3. In the mapping task, define the parameter details.

• Select the Connection Name from the drop down if the connection is parameterized.
• Select the Source Type as Single.
• Select the default data object.
• On the Schedule tab, enter the parameter file directory and file name.

4. In the parameter file, define the data object parameter with the value that you want to use at runtime. Precede the parameter name with two dollar signs ($$). For example, we have a parameter with the name Src_Object and to override the default object EMPLOYESS defined at mapping task level with EMPLOYESS_UPDATED, define the parameter in the following format.

$$Src_Object= EMPLOYESS_UPDATED

The default data object defined in the Mapping task level and the data object defined in the parameter file should have same metadata with same field names and data types.

5. Overriding Source Query using Parameter file in Informatica Cloud

If the mapping is using a Source Query, it can be parameterized and overridden using a parameter file during run time. The process is similar to how the data objects can be parameterized and overridden.

Steps to Override Source Query using Parameter file

1. In the mapping, create a data object parameter.
2. Select Allow parameter to be overridden at runtime option.
3. Use the data object parameter as the source object in the mapping.
4. In the mapping task, on the Sources tab, select Query as the source type.
5. Enter a default custom query.
6. On the Schedule tab, enter the parameter file directory and file name.
7. In the parameter file, enter the query to use when the task runs at runtime.

When you parameterize the SQL query, the fields in the overridden query must be the same as the fields in the default query. The task fails if the query in the parameter file contains fewer fields or is invalid.

Related Article:
HOW TO: Parameterize Source and Target Connections in Informatica Cloud using Taskflows?