I know how I redirect rows on error or truncation. That works if you have an error while you insert a value into the column.
- Truncation: Say you have a string of 10 characters and you insert it into a column of
char(5)
. That is a truncation that you can redirect to an error output table. - Error: Say you insert an
int
into avarchar(10)
column. The data types will not match and the row is redirected to the error output table.
But I do not seem to get a row redirected if an error happens inside the calculation of a column, and at Changing MaximumErrorCount when executing maintenance plan, I was asked to make my remark a new question:
After changing MaximumErrorCount to a high number, I still did not get any redirected rows to the error table. It just stopped the job, but I would want the job to go on and throw only the erroneous rows out to the error table. My problem was that I casted a number as
AS DECIMAL(4,2)
) even though it had to beAS DECIMAL(5,2)
), see how to resolve DTS_E_OLEDBERROR. in ssis. But such an error should normally just redirect the row to the error table and not stop the job, shouldn't it?
The string column could be built as such: check whether a number is bigger than 9 and output the string "warning, high number" in a column called "Warnings and checks".
If then after years, a number 100
pops up the first time ever as a clear outlier that was never thought to happen, and if that number is cast to AS DECIMAL(4,2)
inside such an "if condition" of a column even though it has to be AS DECIMAL(5,2)
, there is an error that stops the SSIS job.
See also SQL Query not throwing error even if the entered scale value is higher than specified for another data type (here: numeric
) question that would throw the same error.
I want the SSIS job to go on and just redirect the row to the error table also if an error happens inside an "if condition". Changing the MaximumErrorCount
to a high number does not fix it. Can it be done at all? Should it be done at all?
select @@version
givesMicrosoft SQL Server 2016 (SP3-CU1-GDR) (KB5029187) - 13.0.7029.3 (X64) Aug 16 2023
for the SSIS server. The other servers areMicrosoft SQL Server 2019 (RTM-CU25) (KB5033688) - 15.0.4355.3 (X64) Jan 30 2024
.TRY_CONVERT
works for you?TRY_CONVERT
is NULL-->error output and NOT NULL goes to the destination. Which means the job will not stop, the redirected rows needs to be captured as varchar and later you can take action on those.