Pre SQL and Post SQL in Informatica

In this article, we are going to explain, How to use Pre SQL and Post SQL in Informatica. Or, the steps involved in configuring the Pre SQL and Post SQL in Source Qualifier Transformation with an example.

For this Informatica Pre SQL and Post SQL example, we are going to use the below show data (EmpDetails table)

Pre SQL and Post SQL in Informatica 1

And the Destination table is Pre and Post SQL Employees. As you can see, it is an Empty table

Pre SQL and Post SQL in Informatica 2

Pre SQL and Post SQL in Informatica Example

Before we start configuring the Pre SQL and Post SQL in Informatica, First connect to Informatica repository service by providing the Admin Console  Username and password that you specified while installing the Informatica Server.

Create Pre SQL and Post SQL in Informatica Source Definition

Navigate to Source Analyzer and define your Sources. Here, we are using the EmpDetails table from the SQL Server database as our source definitions. I suggest you refer Database Source in Informatica to understand the creation of source definition

Pre SQL and Post SQL in Informatica 3

Create Pre SQL and Post SQL in Informatica Target Definition

Please navigate to Target Designer to define the Target. In this example, we are using the existing SQL table as our target definition. You can refer to Create Target table using Source Definition to understand the process of creating a target definition

Pre SQL and Post SQL in Informatica 4

Create Informatica Pre SQL and Post SQL Mapping

To create a new mapping, Please navigate to the Mappings menu and select the Create.. option. It opens the Mapping Name window to write a unique name ( m_Pre_and_Post_SQL) and click the OK button.

Pre SQL and Post SQL in Informatica 5

Drag and drop the EmpDetails source definitions from Sources folder to the mapping designer. Once you drag the source, the Power Center designer will automatically create the Source Qualifier for you.

Pre SQL and Post SQL in Informatica 6

Double click on the Source Qualifier transformation, and go to properties Tab to configure the Informatica Pre SQL and Post SQL

Pre SQL: This property is used to run the SQL Command against the Source before the Integration Service Starts. Click the arrow

Pre SQL and Post SQL in Informatica 7

Here we are writing an Insert Statement to insert new records into the Emp Details table before the integration service starts.

Pre SQL and Post SQL in Informatica 8

Post SQL: Use this property to run the SQL Command against the Source After the Integration Service Ends. Here, we are writing a Delete Statement to delete the record whose employee Id is equal to 9

Pre SQL and Post SQL in Informatica 9

You can also alter the remaining properties. But for now, we are clicking OK button to close the properties windows

Pre SQL and Post SQL in Informatica 10

Next, Drag and drop the target definition (PreandPostSQLEmployees) from Targets folder to the mapping designer. Next, join the source qualifier Transformation with the target definition using the Autolink.. option.

Before we close the Mapping, Let us Save, and Validate the mapping by going to Mapping Menu bar, and select the Validate option.

Pre SQL and Post SQL in Informatica 11

Create Informatica Pre SQL and Post SQL Workflow

After we finish creating the Informatica Pre SQL and Post SQL Mapping, we have to create the workflow for it. Power Center Workflow manager provides two approaches to create a workflow.

In this example, we will create the Workflow manually. First, navigate to Workflows Menu and select the Create option will open Create Workflow window. Please provide the unique name (wf_Pre_and_Post_SQL) and leave the default settings.

Pre SQL and Post SQL in Informatica 13

Once we created the workflow, our next step is to create a session task for our mapping.

Create Session for Pre SQL and Post SQL

There are two types of sessions in Informatica:

For this Informatica Pre SQL and Post SQL example, we created a Non-reusable Session and named it as s_Pre_and_Post_SQL for the session.

Pre SQL and Post SQL in Informatica 12

Please link the Start Task, and the Session Task. Next, navigate to Workflows Menu and select the Validate option to validate the Workflow. From the below screenshot, you can observe that the workflow is a valid one.

Pre SQL and Post SQL in Informatica 14

Now, Let me start the Informatica Pre SQL and Post SQL Workflow. To do so, navigate to the Workflows menu and select the Start Workflow option.

Let us open the SQL Server Management Studio. As you can, it has 16 records: 15 from EmpDetails table, and 1 records from Insert Statement in Pre SQL

Pre SQL and Post SQL in Informatica 15

If you observe the original Table after the Integration process. There are 15 records

The Delete Statement in Post SQL deleted the record with EmpID 9, and The Insert Statement in Pre SQL inserts new records.

Pre SQL and Post SQL in Informatica 16

Comments are closed.