August 9, 2012 at 12:12 am
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?
August 9, 2012 at 12:30 am
A common scenario is to use a dataflow with the OLE DB Destination with the Fast Load option. Set the batch size smaller than the default, for example 50,000 rows. Connect the error output to another OLE DB Destination, but this time without the Fast Load option. Connect the error output of the second OLE DB Destination to whatever component you need. When a batch fails, it will be redirected to the second destination, which will re-attempt the insert, but on a row by row basis, allowing you to extract the individual error rows.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 9, 2012 at 1:28 am
Hi Koen,
can we find the error columns(Apart from Rows i need exact column) also using this method?
If No...can you suggest any alternatives?
And also if the data is HUGE..how does this effect the performance?
August 9, 2012 at 1:38 am
Unfortunately, finding the exact error column is not that straight forward. It's possible though:
SSIS: How to get name of ErrorColumn from Error Output?[/url]
The solution will affect performance negatively.
First of all, there will be a bit more logging as you have more batches to process, so make sure you use the simple recovery model in the destination database.
Secondly, if you have a lot of error rows spread out in the dataset, you'll have lots of batches failing. This will result in many rows being processed row by row, which is tremendously slow.
There's a trade-off to make: speed versus precision (aka finding the exact error rows).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply