The Integration Service returns row errors when it encounters a connection error or syntax error. The SQL transformation has the following default ports to output error text:
SQLError.
Returns database errors when the SQL transformation runs in query mode.
ScriptError.
Returns database errors when the SQL transformation runs in script mode.
When the SQL query contains syntax errors, the error 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 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
When a query contains multiple statements, and you configure the SQL transformation to continue on SQL error, the SQL transformation might return rows from the database for one query statement, but return database errors for another query statement. The SQL transformation returns any database error in a separate row.
When you configure a pass-through port or the NumRowsAffected port, the SQL transformation returns at least one row for each source row. When a query returns no data, the SQL transformation returns the pass-through data and the NumRowsAffected values, but it returns null values in the output ports. You can remove rows with null values by passing the output rows through a Filter transformation.
The following tables describe the output rows that the SQL transformation returns based on the type of query statements.
The following table describes the rows the SQL transformation generates for UPDATE, INSERT, or DELETE query statements:
NumRowsAffected Port or Pass-Through Port Configured
| | |
|
| |
One row with the error in the SQLError port. |
| |
One row for each query statement with the NumRowsAffected or the pass-through column data. |
| |
One row with the error in the SQLError port, the NumRowsAffected port, or the pass-through port data. |
The following table describes the number of output rows the SQL transformation generates for SELECT statements:
NumRowsAffected Port or Pass-Through Port Configured
| | |
| |
Zero or more rows, based on the rows returned from each SELECT statement. |
| |
One row greater than the sum of the output rows for the successful statements. The last row contains the error in the SQLError port. |
| |
One or more rows, based on the rows returned for each SELECT statement:
If NumRowsAffected is enabled, each row contains a NumRowsAffected column with a value zero.
If a pass-through port is configured, each row contains the pass-through column data. When the query returns multiple rows, the pass-through column data is duplicated in each row.
|
| |
One or more rows, based on the rows returned for each SELECT statement. The last row contains the error in the SQLError port:
When NumRowsAffected is enabled, each row contains a NumRowsAffected column with value zero.
If a pass-through port is configured, each row contains the pass-through column data. When the query returns multiple rows, the pass-through column data is duplicated in each row.
|
The following table describes the number of output rows the SQL transformation generates for DDL queries such as CREATE, DROP, or TRUNCATE:
NumRowsAffected Port or Pass-Through Port Configured
| | |
|
| |
One row that contains the error in the SQLError port.
|
| |
One row that includes the NumRowsAffected column with value zero and the pass-through column data.
|
| |
One row with the error in the SQLError port, the NumRowsAffected column with value zero, and the pass-through column data.
|
ASK INFAPreview
