Session Properties in Informatica: Complete Tutorial

A session is a set of instructions that tells Informatica how and when to move the data from sources to targets.

A session is a task, just like the other tasks that we create in Workflow Manager. Any session you create must have a mapping associated with it.

A session can have a single mapping at a time and once assigned, it cannot be changed. To execute a session task, it must be added to a workflow.

A session can be a reusable object or non-reusable. When you create a session in Task Developer, then it can be reused, but when you create a session in Workflow Designer, then it is non-reusable.

A reusable session can be added to multiple workflows.

Properties of Session

Using the properties of the session you can configure various characteristics of the session like pre and post SQL scripts, log file name and path, memory properties, etc.

You can also override mapping properties in the session properties. In this section, we will discuss the following important properties of the session.

• Treat source rows as
• Commit Interval
• Session log file Property
• Test Load Property
• Log options
• Error Handling
• Source/Target Properties
• Connections

Step 1) Open the session “s_m_emp_emp_target” in Task Developer, which we created in the earlier tutorial.

Step 2) Double-click the session icon inside Task Developer to open the Edit Task window.

Step 3) Inside the Edit Task window, click the Properties tab.

Step 4) In the Properties tab, Informatica shows the properties of the session.

Treat Source Rows As Property

This property allows you to define how the source data affects the target table. For example, you can define that the source record should be inserted or deleted from the target.

This property has four options –

• Insert
• Update
• Delete
• Data driven

All four options sit in one drop-down beside the property name.

• When this property is set to insert, the source data will be marked to be inserted. It means the data will only be inserted.
• When the property is set to update, the target data will be updated by the source data. For updating of data, a primary key needs to be defined in the target table.
• When the property is set to delete, the source data which is already present in the target will be deleted from the target table. For this property to execute and apply the changes, the primary key should be defined in the target table.
• With the property set to Data driven, Informatica checks what source records are marked. If the source records are marked as insert in a mapping, then records will be inserted into the target. If records are marked as update in the mapping, then the records will be updated in the target. So the operation performed at the target depends on how records are handled inside the mapping.

Data driven is the option to choose when the mapping holds an Update Strategy transformation, because that transformation flags every row individually.

How to Make Treat Source Rows – Delete

Step 1) In the Properties tab of the session task:

1. Select the “Delete” option in “Treat source rows as”
2. Select the OK button

Step 2) To define a primary key in the target table, open Informatica Designer.

1. Open Target Designer
2. Double-click the “emp_target” table

This will open an “Edit Table” window for our target table.

Step 3) In the edit window of the target table:

1. For the EmpNo column, select the key type as “primary key” from the drop-down menu, and
2. Select the OK button.

Step 4) Save the changes in Informatica and execute the workflow for this mapping.

When you execute this mapping, the source records which are already present in the target will get deleted.

Commit Interval – Property

This property defines the interval after which Informatica makes a commit operation to the target table.

For example, if you are inserting 20,000 records in a target table, and you define commit interval as 5,000, then after every 5,000 insertions of records in the target, a commit operation will be performed.

The default interval is 10,000 rows, and the commit type can be set to target based or source based.

Session Log File Name & Session Log File Directory

Configure this property to modify

• Default session log file name and
• Path of the log file

The $PMSessionLogDir\ is an Informatica variable and on Windows it points to the following default location “C:\Informatica\9.6.1\server\infa_shared\SessLogs”.

Enable Test Load

Using this property, you can test your session and mappings. When you use this feature and execute the sessions, records are fetched from the sources but they do not get loaded into the target. So this feature helps in testing the correctness of mappings, parameter files and the functioning of various transformations inside the mapping.

If you enable this feature, then there is another property – Number of Rows to Test – which should be configured for the number of records you want to be fetched from the source for the test load.

Memory Properties

Memory properties give us the flexibility to fine tune the memory allocated to Informatica for performance optimizations. When there are heavy bottlenecks and performance is poor, then you can try to improve the performance using the memory properties.

To configure memory properties, click the Config Object tab of the Edit Task window. It will open another window where you can configure the changes.

In this section, you can configure the memory properties. For example, default buffer block size, sequential buffer length, etc. Changes to these properties determine how much memory should be allocated to Informatica services for their operation.

Log Options

In this property section, you can configure the log properties of the session. You can set how many logs you want to save for a session and the maximum size of the session log file.

Error Handling

In this section, you can configure the error properties for the session.

Using Stop on errors, you can configure after how many errors the session has to be stopped.

Using Override tracing, you can override the mapping tracing levels.

You can also configure the behaviour of the session for various errors encountered, for example stored procedure error, pre-post SQL error, etc.

Mapping and Source/Target Properties

In the Mapping tab of the Edit Task window, you can configure the properties of the mapping and of its sources, targets and transformations. Table names can be overridden and table name prefixes applied here, and the transformation properties set inside the mapping can be reviewed and overridden as well. It is a single place to review all of them.

Connection Properties in Mapping

Using this property, you can define database connections for the sources and targets.

Source Properties

In this section, you can configure the properties related to the source of the mapping. You can configure pre and post SQL scripts for the source.

Using the SQL query property, you can override the SQL for the source. You can also override the source table name in this section.

Target Properties

In this section, you can configure the details of the target. You can define whether the target load has to be a bulk load or a normal load.

Target load type	What it means for the run
Normal	Rows are written through the database log, so the load is slower but database recovery after a failure is possible.
Bulk	The redo log buffers are bypassed, so the load is faster, but recovery of the failed load is not available.

You can also define the property to truncate the target table before populating it, so the table is emptied first and the load runs afterwards. This property is useful when we create mappings for stage load.

We can also define target table pre SQL and post SQL. Pre SQL is the piece of SQL code which will be executed before performing insert in the target table, and post SQL code will be executed after the load of the target table is completed.

Success or Failure of Session Task

When a workflow holds multiple sessions, one or more of them can fail while the rest succeed, which raises the question of what the workflow status should be. To handle such conditions, Informatica provides a failure specific property inside the workflow. To configure such behaviour –

Step 1) Open the workflow “wkf_run_command”, which we created earlier.

Step 2) Double-click the command task. This will open the Edit Task window.

1. Select the General tab
2. Select the check box against the property “fail parent if this task fails”
3. Select the OK button

Step 3) Double-click the session task. This will open the Edit Task window.

1. Select the General tab
2. Select the check box against the property “fail parent if this task fails”
3. Select the OK button

Step 4) Save the changes using the Ctrl+S shortcut.

When you execute this workflow after making the above changes, if any of the tasks fails the workflow status will be set to failed, so you can identify that during the execution of your workflow some of its tasks have failed.

FAQs