Input Row to Output Row Cardinality
When the Data Integration Service runs a SELECT query, the SQL transformation returns a row for each row it retrieves. When the query does not retrieve data, the SQL transformation returns zero or one row for each input row.
- Query statement processing
- When a SELECT query is successful, the SQL transformation might retrieve multiple rows. When the query contains other statements, the Data Integration Service might generate a row that contains SQL errors or the number of rows affected.
- Port configuration
- The NumRowsAffected output port contains the number of rows that an UPDATE, INSERT, or DELETE statement changes for one input row. The SQL transformation returns the number of rows affected for each statement in a query. When the SQL transformation contains pass-through ports, the transformation returns the column data at least one time for each source row.
- The maximum row count configuration
- The Max Output Row Count limits the number of rows the SQL transformation returns from SELECT queries.
- Error rows
- The Data Integration Service returns row errors when it encounters connection errors or syntax errors. The SQL transformation returns errors to the SQLError port.
- Continue on SQL Error
- You can configure the SQL transformation to continue processing when there is an error in an SQL statement. The SQL transformation does not generate a row error.
Query Statement Processing
The type of SQL query determines how many rows the SQL transformation returns. The SQL transformation can return zero, one, or multiple rows. When the query contains a SELECT statement, the SQL transformation returns each column from the database to an output port. The transformation returns all qualifying rows.
The following table lists the output rows that the SQL transformation generates for different types of query statements when no errors occur in query mode:
Query Statement | Output Rows |
|---|
UPDATE, INSERT, DELETE only | One row for each statement in the query. |
One or more SELECT statements | Total number of database rows retrieved. |
DDL queries such as CREATE, DROP, TRUNCATE | One row for each statement in the query. |
Port Configuration
When you enable Include Statistics as Output, the Developer tool creates the NumRowsAffected port. The Data Integration Service returns at least one row with the NumRowsAffected based on the statements in the SQL query.
The following table lists the output rows the SQL transformation generates if you enable NumRowsAffected:
Query Statement | Output Rows |
|---|
UPDATE, INSERT, DELETE only | One row for each statement with the NumRowsAffected for the statement. |
One or more SELECT statements | Total number of database rows retrieved. NumRowsAffected is zero in each row. |
DDL queries such as CREATE, DROP, TRUNCATE | One row with zero NumRowsAffected. |
Maximum Output Row Count
You can limit the number of rows that the SQL transformation returns for SELECT queries. Configure the Max Output Row Count property to limit number of rows. When a query contains multiple SELECT statements, the SQL transformation limits total rows from all the SELECT statements.
For example, you set Max Output Row Count to 100. The query contains two SELECT statements:
SELECT * FROM table1; SELECT * FROM table2;
If the first SELECT statement returns 200 rows, and the second SELECT statement returns 50 rows, the SQL transformation returns 100 rows from the first SELECT statement. The SQL transformation returns no rows from the second statement.
To configure unlimited output rows, set Max Output Row Count to zero.
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. |
Continue on SQL Error
You can choose to ignore an SQL error that occurs in a query statement. Enable Continue on SQL Error within a Row. The Data Integration Service continues to run the rest of the SQL statements for the row.
The Data Integration Service does not generate a row error. However, the SQLError port contains the failed SQL statement and error messages.
For example, a query might have the following statements:
DELETE FROM Persons WHERE FirstName = ‘Ed’;
INSERT INTO Persons (LastName, Address) VALUES ('Gein', '38 Beach Rd')
If the DELETE statement fails, the SQL transformation returns an error message from the database. The Data Integration Service continues processing the INSERT statement.
Disable the Continue on SQL Error option to troubleshoot database errors and to associate errors with the query statements that caused the errors.