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 queries, then you can use this transformation.
Here, we show you how to use Informatica SQL Transformation to write transact Queries within the transformation with an example. For this example, we are going to use SP Source and Destination tables.
SQL Transformation in Informatica Example
This example shows how to use SQL Transformation in Informatica to run the Script or Query against a database. Before we start configuring, First, connect with the repository service.
To do so, enter the Admin Console Username and Password you specified while installing the 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 database as our source definitions. Please refer Database Source article.
Create SQL Transformation in Informatica Target Definition
Please navigate to Target Designer to define the Target. In this example, we are using the existing table (SP Destination) as our target definition. You can refer to Create Target table using Source Definition article.
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.
Once you select the Create.. option, a new Mapping Name window opens. Let me provide the mapping name and click the OK button.
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.
Create SQL Transformation in Informatica
To create SQL Transformation in Informatica, Please navigate to the Transformation menu and select the Create.. option
Selecting the Create.. option will open the Create Transformation window. Please select the SQL Transformation and provide the unique name and click on Create button
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 Server query is 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.
- Transformation in Passive Mode, By default, it is in Active mode. By checking this option, you can convert it to Passive Mode.
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
By clicking OK will create an Active SQL Transformation In Informatica for you.
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.
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.
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
It adds an extra column to the output section
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 Query.
It opens an 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?
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
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.
Before we close the Mapping, Let us Save, and Validate the 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.
It opens the Create Workflow window to provide the unique name and leave the default settings
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:
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.
Please provide a unique name for this session. Here, we are naming it as s_Employee. 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.
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 Source as the source information.
$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 below Target as the Target information.
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
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
Lastly, we have to configure the Informatica SQL Transformation. So, click on the 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 Source.
After completing the Session configuration, navigate to Workflows Menu, and select the Validate option to validate the Workflow.
Now, Let me start the SQL Transformation in Informatica Workflow. To do so, navigate to the Workflows menu and select the Start Workflow option.
Open the Management Studio and write the following query.
