0

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 a varchar(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 be AS 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?

9
  • @questonto42, which version of sql server are you using?
    – Biju jose
    Commented Mar 26 at 15:36
  • @Bijujose select @@version gives Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5029187) - 13.0.7029.3 (X64) Aug 16 2023 for the SSIS server. The other servers are Microsoft SQL Server 2019 (RTM-CU25) (KB5033688) - 15.0.4355.3 (X64) Jan 30 2024. Commented Mar 26 at 19:19
  • I guess that the answer to this question is that there is no way to make this an error for the error out table and that this is good since you will always want this to break the flow. If you do not know the needed data type for your column calculations, you should check that before going on at all calculations are not saved. With some bad luck, you might not be able to do the same calculation of the column at some later point in time. The error out table is for data that does not fit. The calculation error instead needs a quick white box test. Commented Mar 26 at 19:27
  • I haven't tried the scenario, but splitting the source data based on TRY_CONVERT works for you?
    – Biju jose
    Commented Mar 27 at 18:24
  • 1
    no this will not output to failed rows, but you can easily split the data based on this method, IF 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.
    – Biju jose
    Commented Mar 27 at 18:38

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.