Access -> MS SQL overflow error

  • Hi everyone,

    I am trying to import attendance data from MS Access DB to MS SQL 2012 with SSIS. Access staff table has text field with IDs matching MS SQL staff table. It is a text field, so, I need to convert it to integer to merge tables during transformation.

    For some reason task fails in a source block with overflow error before it gets to data conversion block. IDs have range from 100 to 410,000. I convert IDs with CLng function, can see preview data, it all works fine, unless ID is greater than 32,000. It looks like source result set cannot hold big ID values.

    Does anyone have any ideas?

    Thank you.

  • Anton Bardin (11/8/2015)


    Hi everyone,

    I am trying to import attendance data from MS Access DB to MS SQL 2012 with SSIS. Access staff table has text field with IDs matching MS SQL staff table. It is a text field, so, I need to convert it to integer to merge tables during transformation.

    For some reason task fails in a source block with overflow error before it gets to data conversion block. IDs have range from 100 to 410,000. I convert IDs with CLng function, can see preview data, it all works fine, unless ID is greater than 32,000. It looks like source result set cannot hold big ID values.

    Does anyone have any ideas?

    Thank you.

    Seems like the destination or SSIS is using a SMALLINT value instead of INTEGER. YOu want to make sure that everything in SSIS is using at least DT_I4.

  • I think so as well, just not sure how I can change it. It is source block, which gives overflow. Next block transforms data to different types, but I have error before it. Don't have much experience with SSIS.

  • Right-click on the source and select Show Advanced Editors. Then click on the Input and Output Properties tab, expand OLE DB Source Output, expand Output Columns, and select the column(s) that need to be changed and manually change the date type for each one to one that will hold the source data. If that doesn't work try the same thing on the External Columns.

  • That's exactly what I needed. Thank you a lot Jack!

    Problem solved.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply