none
Arithmetic Overflow error when loading data through SSIS package

    Question

  • Hi,

    I have a situation where once in 2-3weeks the SSIS package loading a table with ID column as Identity column throws Arithmetic overflow error. When i delete the records loaded in that run, reseed the Identity column and run the SSIS package  again it loads fine.

    Any idea what could be wrong in here?

    Sunday, September 08, 2013 8:41 PM

All replies

  • It could be the data type of Identity column. Please check to see the range each data type supports:

    http://technet.microsoft.com/en-us/library/ms152543.aspx



    Vikash Kumar Singh || www.singhvikash.in

    Sunday, September 08, 2013 9:27 PM
  • What is the source? OLE DB/SQL Server

    What is the datatype of the source column in your table? INT, BIGINT, etc.

    What is the datatype within SSIS?


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Monday, September 09, 2013 6:02 AM
    Moderator
  • It's an Identity Column with Data Type as INT. 
    Monday, September 09, 2013 7:38 AM
  • 1) So a Microsoft SQL Server table with an INT column. Is the data type in SSIS DT_I4 ?

    2) What is the highest number when the overflow error occurs?
    The max value of an int is 2147483647 (TinyInt 255, SmallInt 32767, BigInt 9223372036854775807)


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Monday, September 09, 2013 7:48 AM
    Moderator
  • in SSIS data type is DT_I4. And the highest number when overflow occurs is 2147483647. But when i delete that run's data and re-run the package it runs absolutely fine.
    Monday, September 09, 2013 12:06 PM
  • in SSIS data type is DT_I4. And the highest number when overflow occurs is 2147483647. But when i delete that run's data and re-run the package it runs absolutely fine.

    If the highest ID is 2147483647 then you can't insert new records... the next insert will result in an overflow error.

    Are there that many records in the table? Or are there a lot of deletes and inserts?

    Can you change the data type to BIGINT? Then you can continue to 9223372036854775807 :-)


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Monday, September 09, 2013 12:47 PM
    Moderator
  • Before running the ssis package there are 23563183 records, and only 3413398 records are loaded from the package run. But somehow the ID column value shoots up to the max allowed value.
    Monday, September 09, 2013 2:45 PM
  • The package first deletes data for the current year and reloads the fresh data for the entire year.
    Tuesday, September 10, 2013 4:59 AM
  • Before running the ssis package there are 23563183 records, and only 3413398 records are loaded from the package run. But somehow the ID column value shoots up to the max allowed value.

    What is the value of the Identity Increment?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, September 10, 2013 5:56 AM
    Moderator
  • identity is incremented by a value of 1
    Tuesday, September 10, 2013 6:14 AM
  • Ok if you delete the currect year in each run and then add data again, then you will first have to reseed it. Otherwise the ID will continue with the last know number.

    So either add a reseed after the delete and use the max ID from the table. Something like this in an Execute SQL Task (not tested):

    DECLARE @maxnumber int; 
    
    SELECT @maxnumber = max(id) from yourtable
    
    DBCC CHECKIDENT (yourtable, reseed, @maxnumber) 

    or use a BIGINT, but eventually you will reach the maxnumber of the bigint and have the same problem.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter



    Tuesday, September 10, 2013 6:23 AM
    Moderator
  • I already have Reseed functionality added in the package but somehow it doesn't work. I'll post that Reseed code in the evening.
    Tuesday, September 10, 2013 6:50 AM
  • Any other suggestion?
    Saturday, October 26, 2013 4:33 PM