November 8, 2015 at 7:06 pm
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.
November 9, 2015 at 11:45 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 9, 2015 at 2:53 pm
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.
November 10, 2015 at 8:09 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2015 at 3:55 pm
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