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 Mapping because the Mapping is just a structural definition, and it will move data from Source to destination. There are two types of sessions in Informatica:
- Non-reusable Session Task: The session Task that we created in the Workflow Designer is non-reusable.
- Reusable Task: The Informatica session Task that we created in Task Developer is reusable
NOTE: Before you create a Non-reusable Session in Informatica, we must configure the Workflow manager to communicate with databases and Integration services.
In this article, we will show you how to create Non-reusable Session in Informatica with an example. For this example, we are going to use the Mapping that we created in our previous article.
Create Session in Informatica
Creating a Non-reusable Session in Informatica follows three steps, so you have to follow the same procedure:
- Step 1: Configuring the Source and Destination connection. In this example, we are a connection to the SQL Server Relational Database.
- Step 2: Creating a Workflow manager to communicate with Informatica integration services
- Step 3: Creating Session.
First, connect to the repository service. To connect with the Repository service, we have to provide the Admin Console credentials, so please provide the appropriate Informatica Username and Password and click on the Connect button.
From the below screenshot, you can observe that we are in the Task Developer.
Configuring the Source and Destination connection
Before we start creating Session in Informatica, we have to set the relational database connections. To do this, navigate to Connections Menu and select the Relational option.
Once you select the Relational option, a new window called Relational Connection Browser will open. It helps us to create a connection to our relational database.
For this Informatica session example, we are using SQL as our source database that’s why we are selecting the Microsoft SQL Server as the Select Type.
From the screenshot below, you can observe that we currently don’t have any relational connections. To create a new one, please click on the New button.
Once you click on the New button, a new window called Connection Object Definition will open.
- Name: Please specify the unique name for this connection.
- Username: You have to specify the username to connect with the database.
- Password: Here, provide the password of the user mentioned above.
- Database Name: Please specify the name of the database you will use. For this example, we are using AdventureWorksDW2014.
- Server name: Here, you have to write your server instance name.
Click the Ok button after you fill in the details.
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.
It opens the connection Object definition window for the target. So, please specify the connection name, database name, and the remaining details.
NOTE: Once we have configured the database connections, we must create a workflow within the workflow manager. We will explain this in our next article.
Non-reusable Session in Informatica
To create a Non-reusable Session in Informatica, Please navigate to the Tasks Menu and select the Create option.
Next, you have to enter a New name for this non-reusable session task in Informatica. Here we assigned the name as s_DimProducts_from_SQL_to_SQL
Once you click the Create button, a new Mappings window will open. Here, we must select the mapping we want to associate with this session. From the screenshot below, you can observe that we currently have only one mapping, so we are selecting it.
TIP: Please refer Mapping article to understand the data transformation in the below specified mapping m_DimProducts_from_SQL_to_SQL
Our newly created Informatica session task was added to the Workflow Manager. Before we add or link to the Workflow, we have to change a few properties and configure the connection strings of a Source and target. To do so, please double-click on our session task
Double click on the Informatica Session task will open the following window. We can rename the Session Task to a more meaningful name within the General Tab. Please do not forget to provide a valid description of the Task.
Fail Parent if this Task fails: Please check mark this option. If this session task fails, then it will fail the workflow.
Within the 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 the $Source variable. So, we have to configure the source connection by clicking on the Arrow we marked below.
Once you click on the Arrow, a new window called Connection Browser will open. Since we are using SQL as our source database, we select the Microsoft SQL Server as the Select Type. Next, we are selecting the already created connection, i.e., AdventureWorksDW
TIP: If you want to create a new connection before then, you can use the New button.
$Target connection value: This property will store the relational target information in the $Target variable. So, we have to configure the target connection by clicking on the Arrow we marked below.
For this example, we are using the database as the destination, so we selected the Microsoft SQL Server as the Select Type and target as the Destination database name.
Write backward Compatible Session Log File: If you checkmark this option, Informatica generates the session file in text format so that we can review it later. Otherwise, it will produce only a binary format.
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, the last three sessions will be saved to our Informatica session log file.
- Stop on Errors: Please specify how many errors you want the session to tolerate. For example, if you specify 3, the session runs until it encounters three errors.
Within the mappings tab, we must configure the Source, target Connections, and some common properties. First, configure the source connections by clicking on the Dimproducts source in the Sources folder.
Within the Connections, select the Arrow button beside the relational type to open the Relational Connection Browser. This window helps configure the source connections (creating new, modifying, etc.).
Here, we use the Connection variable we created in our previous step, i.e., $Source.
Now, let us configure the target connections by clicking on the Target_Dimproducts present in the Targets folder.
Within the Connections, select the Arrow button beside the relational type to open the Relational Connection Browser. Here, we are using the Connection variable we created in our previous step, i.e., $Target.
TIP: You can also select the Use Object option and select the Database name also.
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, the bulk type will insert the data faster than Normal, but it does not work on indexed tables.
Truncate target table option: This option will truncate the existing data from the destination table by check-marking. It helps us to insert new data for every successful run.
Once you have finished creating the Informatica session task, we have to Link the workflow with the Session Task.
