locked
Dealing with problem data: Skip, edit query, other options? RRS feed

  • Question

  • I am migrating to SQL Server 2008 R2 from an Oracle 10.2 database that contains pensioner information.  At the suggestion of our new system vendor, the destination tables have been created using "datetime" columns to hold date values.

    The problem I am having is that there is one bad record is my source data where a gentleman has a birth date of March 16, 1650 which falls outside of the range of datetime fields which require dates greater than January 1, 1753.  The long term solution is to correct the problem to it no long appears we have a 363 year old pensioner however due to auditing and legal restrictions this will take several weeks.  In the mean time, I am facing pressure to get this data into SQL Server.

    Because of the problem record, SSMA is only loading about half of the data for that table.   I have Project Settings > Migration > On Error set to "Proceed to next batch" and my batch size is the default of 10,000.  I guess I could reduce the batch size but that still wouldn't give me all the rest of the data.  What I really would like is the ability to have SSMA skip the one bad record.  Alternatively, I'd like to edit the query that SSMA runs against Oracle to add an "order by date_of_birth desc" so that the problem record is the last record loaded as this would have the desired effect of loading all the data but the one problem record.  I don't see any way to control the query that SSMA runs.  Is this possible?  Can anyone make a recommendation on how to get all the data but the one bad record loaded?

    Thanks in advance for your help.

    Ken

    Friday, May 24, 2013 2:38 PM

All replies

  • Hello,

    What about duplicating that table and deleting any bad record on the new table, and then migrating the data from the newly created table?

    If you have to migrate all those bad records, what about migrating the date field as varchar or char?   

    Hope this helps.

    Regards,
    Alberto Morillo
    SQLCoffee.com

    Friday, May 24, 2013 9:25 PM
  • HI Ken,

    Are you able to use SSMS "Import/Export" wizard to move that table? If you right click the DB in SSMS Tasks-->Import... you can then write your own query.

    Hope that can help


    -- Please mark my post as an answer if I helped you to resolve the issue or vote up if it helped, thank you--

    EDIT: I meant to say SSMS, not SSMA. Corrected in section above.


    • Edited by Joscion Friday, June 14, 2013 6:05 AM typp
    Sunday, June 9, 2013 3:07 PM