Loading data to table and error in the last moment


  • Hi,
    I have a situation, I have 100 rows to be loaded to table, but problem is 90th record is bad record, where package fails!!! now when i restart package it should load only 10 records, should not start from beginning because when we have million records it'll be a time consuming!!!
    Thanks in Advance!!!

    SSIS learner
    Friday, February 08, 2013 7:58 AM


  • Unfortunately the data flow cannot be restarted in the middle of a load.
    There are a few options:

    * make sure there are no bad rows before you attempt the insert. This means you'll have to introduce some data quality checks

    * make sure your load is fast enough, so that it is no problem if the entire load has to be restarted

    * change the batch size so the 90 rows are committed before the failure (this will slow the load down unfortunately). When you restart the process, check for the latest row that has been inserted and modify the where clause in the source, so that you only select data you need.

    * implement a lookup that checks if incoming rows are updates or inserts. That way you'll only insert rows that were not inserted yet.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Eileen Zhao Thursday, February 14, 2013 8:28 AM
    Friday, February 08, 2013 8:54 AM