SQL Transformation in Informatica

The SQL Transformation in Informatica is used to write or use SQL Queries in the middle of the transformation. Using this SQL transformation, you can Insert, Delete, or Update rows in a Database. If you are familiar with SQL, then you can use this transformation.

Here, we show you how to use Informatica SQL Transformation to write SQL Queries within the transformation with an example. For this Informatica SQL Transformation example, we are going to use SP Source and Destination tables.

SQL Transformation in Informatica 1

SQL Transformation in Informatica Example

This example shows how to use SQL Transformation in Informatica to run the SQL Script or Query against a database. Before we start configuring, First, connect with the Informatica repository service.

To do so, enter the Admin Console  Username and Password you specified while installing the Informatica Server.

Create SQL Transformation in Informatica Source Definition

Once you connected successfully, Please navigate to Source Analyzer and define your Informatica Sources. In this example, we are using the Sp Source table from the SQL Server database as our source definitions. Please refer Database Source in Informatica article.

SQL Transformation in Informatica 2

Create SQL Transformation in Informatica Target Definition

Please navigate to Target Designer to define the Target. In this SQL Transformation example, we are using the existing SQL table (SP Destination) as our target definition. You can refer to Create Target table using Source Definition article.

SQL Transformation in Informatica 3

Create Informatica SQL Transformation Mapping

To create a new mapping for Informatica SQL Transformation, Please navigate to the Mappings menu and select the Create.. option.

SQL Transformation in Informatica 4

Once you select the Create.. option, a new Mapping Name window opens. Let me provide m_SQLEmp as the mapping name and click the OK button.

SQL Transformation in Informatica 5

Drag and drop the SP Source from Sources folder to the mapping designer. Once you drag the source, Power Center Designer will automatically create the Source Qualifier Transformation (default transformation) for you.

SQL Transformation in Informatica 6

Create SQL Transformation in Informatica

To create SQL Transformation in Informatica, Please navigate to the Transformation menu and select the Create.. option

SQL Transformation in Informatica 7

Selecting the Create.. option will open the Create Transformation window. Please select the SQL Transformation and provide the unique name (SQL_Employee) and click on Create button

SQL Transformation in Informatica 8

Clicking the Create button will pop up a new window called Informatica SQL Transformation.

  • Query Mode: Use this option to write Query directly inside the transformation. You can Insert, Delete, or Update rows in a Database
  • Script Mode: If your SQL Server query saved in a Script file, then you can use this option. If you select this option, then you have to pass the Script file name.
  • DB Type: Please select the Database type. For example, SQL, Oracle, etc.
  • Static Connection: If you want the connection to be static, then select this option.
  • Dynamic Connection: Select this option for Dynamic connection.
  • SQL Transformation in Passive Mode: By default, Informatica SQL transformation is in Active mode. By checking this option, you can convert it to Passive Mode.
SQL Transformation in Informatica 9

For this demo, we will write a Select Query. So, let me change the DB Type to Microsoft SQL Server and select the Query Mode

SQL Transformation in Informatica 10

By clicking OK will create an Active SQL Transformation In Informatica for you.

SQL Transformation in Informatica 11

Let me drag all the fields that are available in source definition to SQL Transformation in Informatica. It automatically creates a set of Input and Output fields.

SQL Transformation in Informatica 12

Double click on the SQL Transformation in Informatica mapping editor to edit or configure the transformation. Under the Ports tab, you can see the auto-generated output ports from the source definition inputs.

SQL Transformation in Informatica 13

SQL Ports: This is the most crucial tab in this transformation. It is where we are going to write a Query or selecting the script. Before we start writing the script, Let me add one extra field because our target table has a New Income column. To do so, click on the New field button

SQL Transformation in Informatica 14

It adds an extra column to the output section

SQL Transformation in Informatica 15

Please rename the column to New Income and change its Native type. Next, change the Native Type of each Input and Output filed to match the Source and target definition. Once you have done with it, click on the Arrow beside the SQL Query.

SQL Transformation in Informatica 16

It opens an SQL Editor with available parameters. As you can see, we are using Select Statement to select Yearly income from the SP Source table and adding 45999 to it for each employee record. And the Query is:

SELECT YearlyIncome + 45999 FROM SPSource
WHERE EmpID = ?EmpID?
SQL Transformation in Informatica 17

Check whether everything is OK or not. Remember, whatever the data that we got from the Select Statement saved as Output and our output field is New Income. So, New Income = Yearly Income + 45999 where Emp ID = 1, 2, 3, .. 15

SQL Transformation in Informatica 18

Next, Drag and drop the target definition (SP Destination) from Target’s folder to the mapping designer. Next, connect the SQL Transformation Output fields with the target definition using the Autolink.. option.

SQL Transformation in Informatica 19

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

Create a SQL Transformation in Informatica Workflow

After you finish creating the Mapping, we have to create the workflow for it. PowerCenter Workflow manager provides two approaches to create a workflow.

In this Informatica SQL Transformation example, we will create the Workflow manually. To do so, Please navigate to Workflows Menu and select the Create option.

SQL Transformation in Informatica 20

It opens the Create Workflow window to provide the unique name (wf_SQLEmployee) and leave the default settings

SQL Transformation in Informatica 21

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

Step 4(a): Create a Session for SQL Transformation in Informatica

There are two types of sessions in Informatica:

For this Informatica SQL Transformation example, we created a Non-reusable Session. To create Non-reusable Session, Please navigate to Tasks Menu and select the Create option.

SQL Transformation in Informatica 22

Please provide a unique name for this session. Here, we are naming it as s_SQLEmployee. Once you click on the Create button, a new window called Mappings will open. Here you have to select the mapping that you want to associate with this session i.e., m_SQLEmp.

SQL Transformation in Informatica 23

Double click on the Session task will open the Edit Tasks window. Within Properties Tab

$Source connection value: This property will store the relational source information in the $Source variable. So, click on the Arrow we marked below, and select the Informatica Source as the source information.

SQL Transformation in Informatica 24

$Target connection value: This property will store the relational target information in the $Target variable. So, click on the Arrow we marked below, and select the Informatica Target as the Target information.

SQL Transformation in Informatica 25

Within the mappings tab, we have to configure the Source and Target Connections. First, let us configure the source connections by clicking on the SQ_SPSource source present in the Sources folder.

Within the Connections, click on the Arrow button beside the Relational type, and use the Connection variable that we created in our previous step, i.e., $Source

SQL Transformation in Informatica 26

Now, we have to configure the Target Connection. So, click on the SPDestination present in the Targets folder. Within the Connections, click on the Arrow button beside the Relational type, and use the Connection variable $Target

SQL Transformation in Informatica 27

Lastly, we have to configure the Informatica SQL Transformation. So, click on the SQL_Employee present in the Transformations folder.

Within the Connections, click on the Arrow button beside the Relational type, and use the Connection variable $Source. It is because we used the Select Statement to select records from Informatica Source.

SQL Transformation in Informatica 28

After completing the Session configuration, navigate to Workflows Menu, and select the Validate option to validate the Workflow.

SQL Transformation in Informatica 29

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

SQL Transformation in Informatica 30

Open the SQL Server Management Studio and write the following SQL Query.

SQL Transformation in Informatica 31