Post reply

Import Data from MS Access problems

  • August 22, 2007 at 10:31 pm

    #153769

    Hi,

    First time user of SSIS....

    Am copying data from MS Access to SQl Server.

    The total number of rows in the destination was less than the source, so I output the error rows to a text file.

    The data in the rows looks okay, but the ErrorCode and ErrorColumn columns contain error codes like

    -1071607686
    -1071607689

    and refers to columns like 249, 221 etc. I have only about 20 columns in the query though.

    How do I trouble shoot this?

    PS, I had to add a data conversion data flow in between the sources as I was getting unicode problems.... how to avoid this?

  • August 23, 2007 at 7:14 am

    #728891

    You can find the correct columns by going to the advanced editor in your data flow tasks.

    1. Right-click on the destination and choose "Advanced Editor"
    2. Go to the "Input and Output Properties" tab
    3. Expand OLE DB Destination Input --> External Columns
    4. Click on each of the columns and the ID will show up in the right window

    Unfortunately (and someone pleasecorrect me if I'm wrong), I've heard there is no way to extract the actual column name causing the problem during run-time.

    Hope this helps.

    Dan

  • August 26, 2007 at 4:31 pm

    #729455

    Hi,

    Ok, I followed your instructions, and it appears that the problem columns are dates.

    In particular, dates entered incorrectly like 01/01/205.

    In DTS I ave been able to program around stuff like this, how do I do this with SSIS? The data conversion Flow I alrady use, and it doesn't seem to have any 'programmability'..

    thanks,

  • August 27, 2007 at 7:20 am

    #729521

    There are actually quite a few "tranformation" tools that you can use in SSIS. You could use a conditional split to send those items that have an incorrect date down another path where you could use a derived column tool to fix the date and then a union all to join the corrected records back to the main stream records.

    Regards,

    Carla Hepker
    Documentation: A Shaft of light into a coded world.

  • August 27, 2007 at 1:52 pm

    #729630

    There is a pretty good website that provides some free, basic SSIS training videos (there is some other goofy stuff there, as well, but some good SSIS stuff):

    http://www.jumpstarttv.com

  • August 27, 2007 at 6:33 pm

    #729677

    thanks, will give it a look.

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

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