SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk Insert error logging


Bulk Insert error logging

Author
Message
Paul_J
Paul_J
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 637
I simply want to log any errors that occur in a Bulk Insert task.

I'm simply going from a flat file to a table and it's all working fine, except for the error logging.

I have logging set up and it's partially working - only a single error is reported.

Say I have five rows in the input file and rows 3 and 5 have "bad" data (incorrect types, too long, whatever). When the Bulk Insert task runs, the "bad" records don't get inserted which is correct.

The problem is that only the first errored line (row 3) gets reported and logged. How can I get ALL errors and warning to get logged?

Cheers
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
SSC Eights!
SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)SSC Eights! (944 reputation)

Group: General Forum Members
Points: 944 Visits: 530
One option I can think of is redirect error to flat file. Just use dataviewers to see how the bad rows gets directed.
Thanks.,
Paul_J
Paul_J
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 637
I already have logging set up to redirect the errors to a flat file.
problem is only the first error is getting thrown.
Paul_J
Paul_J
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 637
It turns out that I'm blind and the single error event that gets raised sends all of the errors in the ErrorDescription variable. So I just need to parse the line and extract the separate messages out of it. Not very neat but I guess it will have to do.

Sorry for the mistake.
Paul_J
Paul_J
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 637
I just realised that I had set the batchsize to something greater than the number of rows in the input file, hence why multiple errors were being put into the error description.

If i set the batch size to 1, then only the first error gets reported.

I'm thinking there must be another setting somewhere in there which will get me what I want - a separate error for each bad row in the file.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search