Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hello,
What are your statements and the query you are running in tMSSQLRow component? Are you using stored procedure?
Best regards
Sabrina
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)