locked
SSIS OLEDB Source SQL overflow Stops the read but no error to SSIS. RRS feed

  • Question

  •  We ran into an issue in a SSIS package with an OLEDB source SQL query that when bad data made it overflow, no SSIS error was raised and the processing stops after some rows were processed.   SSIS execution goes on FAT DUMB and HAPPY!

    That is correct no ERROR was reported.

    I set up a test table with two Int fields and put 2 billion in each.
    In the query OLEDB source we return the two int fields and multiply them by each other.

    "Select int1, int2, (int1 * int2) as total from test_table"

    this gives an error in preview if this is the only record in the table.

    Then I added ten rows before that row with 1,1 then 2,2  etc. until 9,9 in both int.

    Then I added ten rows after the bad row with 11,11 12,12 etc until 99,99 in both int.

    When the job runs it will load 1,1 up to 9,9 and then stop.

    11,11 to 99,99 do not get processed.

    Is there a way to catch and handle the error and stop processing?

    RP
    Friday, February 6, 2009 11:55 PM

Answers

  • please take a look at the following link: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276828.  it's a bug report which states that the execute sql task doesn't fail when a RAISERROR statement is encountered.  my hunch is that you've stumbled upon a similar bug.  i don't for certain if this has been resolved in sql server 2005 or 2008.  please feel free to submit a new bug report to connect if you wish to do so.  if you do so, please let us know so that we may vote on it.

    thanks.
    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Marked as answer by Tony Tang_YJ Friday, February 20, 2009 10:00 AM
    Thursday, February 19, 2009 6:45 AM

All replies

  •  have you tried implementing t-sql try...catch statement http://msdn.microsoft.com/en-us/library/ms175976.aspx ?
    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Proposed as answer by Duane Douglas Saturday, February 7, 2009 8:12 AM
    • Marked as answer by Tony Tang_YJ Friday, February 13, 2009 7:09 AM
    • Unmarked as answer by RPDallas Wednesday, February 18, 2009 9:59 PM
    • Unproposed as answer by RPDallas Wednesday, February 18, 2009 9:59 PM
    Saturday, February 7, 2009 7:37 AM
  • I was worried that we may have other queries that could do this and records missed with no errors being raised.  We found this one by a customer telling us a study (batch) was bigger than what the record count had.   I was hopping there was an Integration services patch.

    I like the try catch, but the code below did not help any in the OLEDB source object in SSIS.
    In the preview it used to show you the error, but with the code below it no longer shows an error, and the SSIS package still does not error off.   is there a SQL error that will fail the read and error the SSIS?   Can my raise error do more?

    Begin Try
     Select total, int1, int2, (int2 * int1) as total2
     from test_table
    End Try
    Begin Catch
    raiserror('failed sql reader',15,1)
    End Catch;

    Let me know if you would like to see the data set.

    RP
    Wednesday, February 18, 2009 9:33 PM
  • please take a look at the following link: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276828.  it's a bug report which states that the execute sql task doesn't fail when a RAISERROR statement is encountered.  my hunch is that you've stumbled upon a similar bug.  i don't for certain if this has been resolved in sql server 2005 or 2008.  please feel free to submit a new bug report to connect if you wish to do so.  if you do so, please let us know so that we may vote on it.

    thanks.
    Duane Douglas, MCAD | http://www.ssisbi.com | Please mark the post(s) that answered your question.
    • Marked as answer by Tony Tang_YJ Friday, February 20, 2009 10:00 AM
    Thursday, February 19, 2009 6:45 AM