Error Handling in Bulk Insert

• goutham.hyd

  SSC Veteran

  Points: 297

  More actions

  August 9, 2012 at 12:13 am

  #259814

  Hi,

  I am using SSIS 2008.

  Can anybody please explain me how do we handle errors while using Bulk Insert? I need to capture the exact row errors and on which column?

  If we cannot do that using Bulk Insert Task is there any other alternative for Fast Load and capture exact row errors?

• Rajib Bahar

  Ten Centuries

  Points: 1007

  More actions

  August 9, 2012 at 10:37 am

  #1523956

  Try some of these options.

  Option A:

  - Use bcp to import data (after dropping all keys/constraints etc)

  - Recreate all the keys/constrains after the data load

  - Run the validation routine in SSIS to flag questionable data

  Option B:

  - If you're using flat-file to import data via SSIS. Look into "Error Output" in Flat-File-Source-Editor

  - Set the behavior (i.e. redirect/fail/ignore) you want to have happened on error/truncation of each column

  I'm curious to learn how others approach this.

  Rajib Bahar
  http://www.rajib-bahar.com
  http://www.twitter.com/rajib2k5
  http://www.youtube.com/icsql