Error with tMSSQLRow Reject Functionality

[qfinleymha]

I have created a job which passes values to tMSSQLRow, with Die on Error unchecked and a rejects stream, and I am able to catch reject rows. However, the stream is causing an issue with committing all statements in tMSSQLRow. The exact scenario is that X number of records are good and do not throw a SQL error. The console says that X number of records have been  committed, however, when i query the table, the number of records is less than X. This mismatch occurs when one of the records are rejected due to SQL error: Conversion failed when converting date and/or time from character string.

Any insight on why this is happening for a particular SQL Exception?

[Anonymous]

Hello,

What are your statements and the query you are running in tMSSQLRow component? Are you using stored procedure?

Best regards

Sabrina

[qfinleymha]

Hi Sabrina,

Each row passed to tMSSQLRow creates an 'INSERT INTO Select' statement. There is no stored procedure. I am using tMSSQLRow instead of tMSSQLOutput because each row contains a different set of columns to insert into. For example, 

tDenormalize (comma separated) --------> tMSSQLROW

row1.columns                                              INSERT INTO tableName (" + row1.columns + ") SELECT " + row1.values + "

row1.values

When i reduce the 'Commit Every' to 1 OR use existing connection and opt for  'Auto Commit', the records load as expected. 

I would not like to commit on every transaction because this job needs to process large data sets (~1M records)