Tutorial Gateway

  • C Programming
  • Java
  • R
  • SQL
  • MySQL
  • Python
  • BI Tools
    • Informatica
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • QlikView
  • About
    • About Us
    • Contact Us

Reusable Session in Informatica

13-01-2016 by suresh Leave a Comment

A Session in Informatica is a set of instructions that tells the Informatica Integration Service when and how to move data from source to target. In Informatica we have to create a session task for each and every mapping because, mapping is just a structural definition and session will actually move data from source to destination. There are two types of sessions in informatica:

  • Non-reusable Session Task: Session Task that we created in Workflow Designer is non-reusable. Please refer Session in Informatica article to understand the steps involved in Non- Reusable Session
  • Reusable Session Task: Session Task that we created in Task Developer is reusable

In this article we will show you, How to create Reusable Session in Informatica with example. For this example, we are going to use the Mapping that we created in our previous article. You can refer Informatica Mapping article for further reference.

Create Reusable Session in Informatica

Creating reusable Session in Informatica follows two steps so you have to follow the same procedure:

  • Step 1: Configuring the Source and Destination connection. In this example, we are connecting to SQL Server relational Database.
  • Step 2: Creating Reusable Session in Informatica

First connect to Informatica repository service. In order to connect with Repository service we have to provide the Informatica Admin Console credentials so, Please provide the appropriate Username and Password and click on Connect button as shown below. From the below screenshot you can observe that, we are in the Target Designer.

Reusable Session in Informatica 1

Configuring the Source and Destination connection

Before we start creating Reusable Session in Informatica we have to configure the relational database connections. In order to do this, Please navigate to Connections Menu and select the Relational option as shown below.

Reusable Session in Informatica 20

Once you select on the Relational option, a new window called Relational Connection Browser will be opened as shown below. This will help us to create connection to our relational database.

Reusable Session in Informatica 21

For this example, we are using the SQL as our source database that’s why we are selecting the Microsoft SQL Server as the Select Type

Reusable Session in Informatica 22

From the below screenshot you can observe that, currently we don’t have any relational connections. In order to create new one, please click on the New button as we shown below.

Reusable Session in Informatica 23

Once you click on the New button, a new window called Connection Object Definition will be opened as shown below.

  • Name: Please specify the unique name for this connection.
  • Username: You have to specify the username to connect with database.
  • Password: Here you have to provide the password of the above mentioned user.
  • Database Name: Please specify the name of a database you are going to use. For this example, we are using AdventureWorksDW2014.
  • Server name: Here you have to write your server instance name.

Click Ok button after you fill the details

Reusable Session in Informatica 24

We need to create one more connection string for our target database. Although we can create using the above mentioned steps we want to copy the above connection using the Copy As.. option.

Reusable Session in Informatica 25

This will open the Connection Object Definition window for the target so, please specify the connection name, database name and the remaining details.

Reusable Session in Informatica 26

Reusable Session in Informatica

In order to create reusable Session in Informatica, Within the Task Developer, Please navigate to Tasks Menu and select the Create option as shown below.

Reusable Session in Informatica 2

Once you select the Create option, Create Task window will be opened. From the drop down list, Please select the Session Task

Reusable Session in Informatica 3

Next, you have to enter New name for this reusable session task. Here we assigned the name as s_DimProducts_from_SQL_to_SQL

Reusable Session in Informatica 4
Once you click on the Create button, a new window called Mappings will be opened. Here we have to select the mapping you want to associate with this session. From the below screenshot you can observe that, currently we have only one mapping so we are selecting it.

TIP: Please refer Informatica Mapping article to understand the data transformation in below specified mapping m_DimProducts_from_SQL_to_SQL

Reusable Session in Informatica 5

Our newly created session task is added to the Task Developer. Before we adding or linking to the Informatica Workflow, we have to change few properties and configure the connection strings of a Source and target. In order to do so, Please double-click on our session task

Reusable Session in Informatica 6

Double click on the Session task will open the following window. Within the General Tab, we can rename the Session Task to more meaningful name. Please do not forget to provide the valid description about the Task.

Reusable Session in Informatica 7

Within Properties Tab, we have three common properties that we have to configure for the relational databases.

$Source connection value: This property will store the relational source information in $Source variable. So, we have to configure the source connection by Click on the Arrow we marked below.

Reusable Session in Informatica 8

Once you click on the Arrow, a new window called Connection Browser will be opened as shown below. Since we are using the SQL as our source database, we are selecting the Microsoft SQL Server as the Select Type

Reusable Session in Informatica 9

Next, we are selecting the already created connection i.e, AdventureWorksDW

TIP: If you want to create new connection then, you can use the New button to create one.

Reusable Session in Informatica 10

$Target connection value: This property will store the relational target information in $Target variable. So, we have to configure the target connection by Click on the Arrow we marked below.

For this example, we are using the SQL database as the destination so, we selected the Microsoft SQL Server as the Select Type and Informatica target as the Destination database name

Reusable Session in Informatica 11

Write backward Compatible Session Log File: If you check mark this option then, informatica will generate the session file in text format so that we can review it later otherwise, it will generate only binary format.

Reusable Session in Informatica 12

Within the Config Object, we have to configure the Log options and Error handling. Although we have many properties, we are discussing and configuring the most common properties within this window.

  • Save Session log for these runs: Please specify the integer number here. For example, if you specify 3 then last three sessions will be saved to our session log file
  • Stop on Errors: Please specify, how many errors you want to tolerate. For example, if you specify 3 then the session will run until it encounter three errors.

Reusable Session in Informatica 13

Within the mappings tab, we have to configure the Source, target Connections and some common properties. First, let us configure the source connections by clicking on the Dimproducts source present in the Sources folder.

With in the Connections, select the Arrow button beside the relational type to open the Relational Connection Browser as shown below. This window will help you to configure the source connections (creating new, modifying etc). Here, we are using the Connection variable that we created in our previous step i.e., $Source

Reusable Session in Informatica 14

Now, let us configure the target connections by clicking on the Target_Dimproducts present in the Targets folder.

With in the Connections, select the Arrow button beside the relational type to open the Relational Connection Browser as shown below. Here, we are using the Connection variable that we created in our previous step i.e., $Target

TIP: You can also select the Use Object option and select the Database name also.

Reusable Session in Informatica 15

Within the Target properties, we have to configure two common properties and they are:

  • Target Load Type: If your target table has indexes then use the Normal type otherwise, Bulk type. Remember, bulk type will insert the data faster than Normal but it does not work on indexed tables.
  • Truncate target table option: By check marking this option will truncate the existing data from the destination table. This will help us to insert fresh data for every successful run.

Reusable Session in Informatica 16

Once you finished creating the session task, we have to Validate the task before using it. In order to do so, Please navigate to Tasks Menu and select the Validate option as shown below.

Reusable Session in Informatica 17

From the below screenshot you can observe that, Our Reusable session in informatica is a valid one.

Reusable Session in Informatica 18

Thank You for Visiting Our Blog

Placed Under: Informatica

Share your Feedback, or Code!! Cancel reply

Trending

  • SQL Server Integration Services (SSIS)
  • SQL Server Tutorial
  • C Program to Print Prime Numbers from 1 to 100
  • C Program to Calculate the Sum and Average of n Number
  • SQL Server Reporting Services (SSRS)
  • Home
  • Types of Functions in C

Stay in Touch!

Sign Up to receive Email updates on Latest Tutorials

  • Blogger
  • C Programs
  • Java Programs
  • SQL
  • SSIS
  • SSRS
  • Tableau
  • JavaScript

Copyright © 2018 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy