Informatica Interview Questions on SQL Transformation

1. What is SQL transformation?

SQL transformation process SQL queries midstream in a pipeline and you can insert, update, delete and retrieve rows from a database.

2. How do you configure a SQL transformation?

The following options are required to configure SQL transformation:

Mode: Specifies the mode in which SQL transformation runs. SQL transformation supports two modes. They are script mode and query mode.

Database type: The type of database that SQL transformation connects to.

Connection type: Pass database connection to the SQL transformation at run time or specify a connection object.

3. What are the different modes in which a SQL transformation runs?

SQL transformation runs in two modes. They are:

Script mode: The SQL transformation runs scripts that are externally located. You can pass a script name to the transformation with each input row. The SQL transformation outputs one row for each input row.

Query mode: The SQL transformation executes a query that you define in a query editor. You can pass parameters to the query to define dynamic queries. You can output multiple rows when the query has a SELECT statement.

4. In which cases the SQL transformation becomes a passive transformation and active transformation?

If you run the SQL transformation in script mode, then it becomes passive transformation. If you run the SQL transformation in the query mode and the query has a SELECT statement, then it becomes an active transformation.

5. When you configure an SQL transformation to run in script mode, what are the ports that the designer adds to the SQL transformation?

The designer adds the following ports to the SQL transformation in script mode:

ScriptName: This is an input port. ScriptName receives the name of the script to execute the current row.

ScriptResult: This is an output port. ScriptResult returns PASSED if the script execution succeeds for the row. Otherwise it returns FAILED.

ScriptError: This is an output port. ScriptError returns the errors that occur when a script fails for a row.

6. What are the types of SQL queries you can specify in the SQL transformation when you use it in query mode.

Static SQL query: The query statement does not change, but you can use query parameters to change the data. The integration service prepares the query once and runs the query for all input rows.

Dynamic SQL query: The query statement can be changed. The integration service prepares a query for each input row.

7. What are the types of connections to connect the SQL transformation to the database available?

Static connection: Configure the connection object tin the session. You must first create the connection object in workflow manager.

Logical connection: Pass a connection name to the SQL transformation as input data at run time. You must first create the connection object in workflow manager.

Full database connection: Pass the connect string, user name, password and other connection information to SQL transformation input ports at run time.

8. How do you find the number of rows inserted, updated or deleted in a table?

You can enable the NumRowsAffected output port to return the number of rows affected by the INSERT, UPDATE or DELETE query statements in each input row. This NumRowsAffected option works in query mode.

9. What will be the output of NumRowsAffected port for a SELECT statement?

The NumRowsAffected output is zero for the SELECT statement.

10. When you enable the NumRowsAffected output port in script mode, what will be the output?

In script mode, the NumRowsAffected port always returns NULL.

11. How do you limit the number of rows returned by the select statement?

You can limit the number of rows by configuring the Max Output Row Count property. To configure unlimited output rows, set Max Output Row Count to zero.