SQL Transformation Ports
When you create an SQL transformation, the Developer tool creates the SQLError port by default. Add input ports, output ports, and pass-through ports in the Ports view.
The SQL transformation has the following types of ports:
- Input
- Receives source data that you can use in an SQL query.
- Output
- Returns database data from an SQL SELECT query.
- Pass-through
- Input-output ports that pass source data through the transformation without changing it.
- SQLError
- Returns SQL errors from the database. If no errors occur, returns NULL.
- NumRowsAffected
- Returns the total number of database rows affected by INSERT, DELETE, and UPDATE query statements for an input row. The Developer tool creates this port when you choose to include the update statistics in the output row.
- Return Value
- Receives the return value from a stored procedure.
Input Ports
You can reference SQL transformation input ports with parameter binding in any type of SQL statement or stored procedure. You can create input ports in the SQL transformation for data that you do not intend to pass to output ports.
You must manually add ports if you are configuring an SQL query that has input parameters. When you import a stored procedure to the SQL transformation, the SQL transformation creates the input ports. You can add pass-through ports to pass data through the transformation without changing it.
You can add ports in the Overview view. When you add a port, enter the native datatype for the port. The native datatype is a datatype that is valid for the database that you are connecting to. When you configure a native datatype, a transformation datatype appears. If you drag rows to the SQL transformation, the Developer tool sets the native datatype based on datatypes that are valid for the database you are connecting to. Verify that the datatypes for columns that you use in the query are the same datatypes as the columns in the database.
The following figure shows the CreationDate input port in a reusable SQL transformation:
To add input ports, click Input in the Ports panel. Click New.
Note: If you select Copy to Output for a port, the input port becomes a pass-through port. Pass-through ports appear in the Input and Output sections of the Ports view.
Output Ports
SQL transformation output ports return values from a query statement or from a stored procedure.
You must define the output ports when you manually configure a SQL transformation. Define an output port for each stored procedure output parameter or for each port that a SELECT statement returns.
When you import a stored procedure, the Developer tool creates an output port for each output parameter that the procedure returns. If the procedure returns a result set, you must manually define the output ports in the result set. A stored procedure can return a result set and it can return output parameters that are not part of the result set in the same run. You must define the output ports for the result set fields and for the output parameters.
When you configure an output port, choose the native datatype for the port. The output port native datatype must match the datatype of the corresponding column in the database. When you configure the native datatype, the Developer tool defines the transformation datatype for the port.
For example, the SQL transformation contains the following SQL query for an Oracle database:
SELECT FirstName, LastName, Age FROM EMPLOYEES
You might configure the following output ports and the native datatypes in the SQL transformation:
Output Port | Native Datatype | Transformation Datatype |
|---|
FirstNm | varchar2 | string |
LastNm | varchar2 | string |
Age | number | double |
The number of the output ports and the order of the output ports must match the number and the order of the columns that the query or stored procedure returns. When the number of output ports is more than the number of columns in the query or stored procedure, the extra ports return a null value. When the number of output ports is less than the number of columns in the SQL, the Data Integration Service generates a row error.
If you change the database type that the transformation connects to, the Developer tool changes the native types of the output ports. The Developer tool might not choose the correct datatype for all the ports. If you change the database type, verify that the native datatype for each output port is the same datatype as the column in the database. For example, the Developer tool might choose nVarchar2 for a Oracle database column. You might need to change the datatype to varchar2.
Configure output ports in the SQL transformation Overview view.
Pass-through Ports
Pass-through ports are input-output ports that pass data through the transformation without changing the data. The SQL transformation returns data in the pass-through ports whether an SQL query returns rows or not.
When the input row contains a SELECT query statement, the SQL transformation returns the data in the pass-through port for each row it returns from the database. If the query result contains multiple rows, the SQL transformation repeats the pass-through data in each row.
When a query returns no rows, the SQL transformation returns the pass-through column data with null values in the output columns. For example, queries that contain INSERT, UPDATE, and DELETE statements return no rows. When the query has errors, the SQL transformation returns the pass-through column data, the SQLError message, and null values in the output ports.
You cannot configure a pass-through port to return data from a SELECT query.
To create a pass-through port, create an input port and select Copy to Output. The Developer tool creates an output port and adds an “_output” suffix to the port name. You cannot change the output port that the Developer tool creates for a pass-through port. You cannot create an output port with the "_output" suffix.
The following figure shows a Name pass-through port in a reusable SQL transformation:
SQLError Port
The SQLError port returns SQL errors from the database from stored procedures or SQL queries.
The following figure shows the SQLError port in a reusable SQL transformation:
When the SQL query contains syntax errors, the SQLError port contains the error text from the database. For example, the following SQL query generates a row error from an Oracle database:
SELECT Product_ID FROM Employees
The Employees table does not contain Product_ID. The Data Integration Service generates one row. The SQLError port contains the error text in one line:
ORA-0094: “Product_ID”: invalid identifier Database driver error... Function Name: Execute SQL Stmt: SELECT Product_ID from Employees Oracle Fatal Error
You can configure multiple query statements in the SQL query or you can call multiple stored procedures. When you configure the SQL transformation to continue on SQL error, the SQL transformation might return rows for one query statement, but return database errors for another query statement. The SQL transformation returns any database error in a separate row.
Number of Rows Affected
Enable the NumRowsAffected output port to return the number of rows that the INSERT, UPDATE, or DELETE query statements change for each input row. You can configure the NumRowsAffected output port for SQL queries.
The Data Integration Service returns the NumRowsAffected for each statement in the query. NumRowsAffected is disabled by default.
When you enable NumRowsAffected and the SQL query does not contain an INSERT, UPDATE, or DELETE statement, NumRowsAffected is zero in each output row.
When the SQL query contains multiple statements, the Data Integration Service returns the NumRowsAffected for each statement. NumRowsAffected contains the sum of the rows that the INSERT, UPDATE, and DELETE statements change for an input row.
For example, a query contains the following statements:
DELETE from Employees WHERE Employee_ID = ‘101’;
SELECT Employee_ID, LastName from Employees WHERE Employee_ID = ‘103’;
INSERT into Employees (Employee_ID, LastName, Address)VALUES (‘102’, 'Gein', '38 Beach Rd')
The DELETE statement affects one row. The SELECT statement does not affect any row. The INSERT statement affects one row.
The Data Integration Service returns one row from the DELETE statement. NumRowsAffected is equal to one. The Data Integration Service returns one row from the SELECT statement, NumRowsAffected is zero. The Data Integration Service returns one row from the INSERT statement with NumRowsAffected equal to one.
