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 example. For this example, we are going to use the below show data (EmpDetails table)
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 Example
TIP : Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Step 1: Create Source Definition for Pre SQL and Post SQL
Once you connected successfully, Please navigate to Source Analyzer and define your Sources. As we said before, we are using EmpDetails table from SQL Server database as our source definitions. I suggest you to refer Database Source in Informatica to understand the creation of source definition
Step 2: Create Target Definition for Pre SQL and Post SQL
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 Create Informatica Target table using Source Definition to understand the process of creating target definition
Step 3: Create Mapping for Pre SQL and Post SQL
To create a new mapping, Please navigate to Mappings menu in Menu Bar and select the Create.. option. This will open the Mapping Name window to write unique name for this mapping. Let me write m_Pre_and_Post_SQL and click OK button.
TIP : Please refer Informatica Mapping article to understand the procedure for creating Mapping
Drag and drop the EmpDetails source definitions from Sources folder to the mapping designer. Once you drag the source, Power Center designer will automatically create the source qualifier for you. I suggest you to refer Source Qualifier Transformation article
Double click on the Source Qualifier transformation, and got o properties Tab to configure the 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
Here we are writing an Insert Statement to insert new records in to the Emp Details table before the integration service starts.
Post SQL: Use this property to run the SQL Command against the Source After the Integration Service Ends. Here, we writing a Delete Statement to delete the record whose employee Id is equal to 9
You can also alter the remaining properties. But for now, we are clicking OK button to close the properties windows
Next, Drag and drop the target definition (PreandPostSQLEmployees) from Targets folder to the mapping designer. Next, connect the source qualifier Transformation with the target definition. Please use the Autolink.. option to connect them.
Before we close the Mapping, Let us Save, and Validate the mapping by going to Mapping Menu bar, and select the Validate option.
Step 4: Create Workflow for Pre SQL and Post SQL
After we finish creating the Mapping, we have to create the workflow for it. Power Center Workflow manager provides two approaches to create workflow.
In this example we will create the Workflow manually. To do so, Please navigate to Workflows Menu and select the Create option. This will open Create Workflow window as shown below. Please provide the unique name (wf_Pre_and_Post_SQL) and leave the default settings.
Once we created the workflow, our next step is to create session task for our mapping.
Step 4(a): Create Session for Pre SQL and Post SQL
There are two types of sessions in informatica:
- Non-reusable Session Task: Please refer Session in Informatica article.
- Reusable Session Task: Refer Reusable Session in Informatica article.
For this example we created a Non-reusable Session, and named it as s_Pre_and_Post_SQL for the session.
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.
Now, Let me start the Workflow. To do so, navigate to Workflows menu, and select the Start Workflow option.
Let us open the SQL Server Management Studio to check whether we successfully transfer the data from source or not. As you can, it has 16 records: 15 from EmpDetails table, and 1 records from Insert Statement in Pre SQL
If you observe the original Table after the Integration process. There are 15 records: Delete Statement in Post SQL deleted the record with EmpID 9, and New records is Inserted by the Insert Statement in Pre SQL.
Thank You for visiting Our Blog