Developer Transformation Guide

10.4.0
- 10.5.2
- 10.5
- 10.4.1

Back Next

Error Rows

The Data Integration Service returns row errors when it encounters a connection error or syntax error. The SQL transformation returns SQL errors to the SQLError port.

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 table describes the rows that the SQL transformation generates for UPDATE, INSERT, or DELETE query statements:

NumRowsAffected Port or Pass-Through Port Configured	SQLError	Rows Output
Neither port configured	No	One row with NULL in the SQLError port.
Neither port configured	Yes	One row with the error in the SQLError port.
Either port configured	No	One row for each query statement with the NumRowsAffected or the pass-through column data.
Either port configured	Yes	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 that the SQL transformation generates for SELECT statements:

NumRowsAffected Port or Pass-Through Port Configured	SQLError	Rows Output
Neither port configured	No	One or more rows, based on the rows returned from each SELECT statement.
Neither port configured	Yes	One row greater than the sum of the output rows for the successful statements. The last row contains the error in the SQLError port.
Either port configured	No	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.
Either port configured	Yes	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 that the SQL transformation generates for DDL queries such as CREATE, DROP, or TRUNCATE:

NumRowsAffected Port or Pass-Through Port Configured	SQLError	Rows Output
Neither port configured	No	One row with NULL in the SQLError port.
Neither port configured	Yes	One row that contains the error in the SQLError port.
Either port configured	No	One row that includes the NumRowsAffected column with value zero and the pass-through column data.
Either port configured	Yes	One row with the error in the SQLError port, the NumRowsAffected column with value zero, and the pass-through column data.

Rename Saved Search

Table of Contents

Developer Transformation Guide

Developer Transformation Guide

Error Rows

Error Rows