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.
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 NumRowsAffectedoutout 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.
0 comments:
Post a Comment