October 21, 2014 at 2:49 am
Hi,
I am having flat file as a source which is coma separated, which i want to load into sql table,
While loading if this file if file contains an error i.e. for example delimiter is not found or any other issue in file then i want to capture that particular row and redirect it to flat file. Remaining rows i want to load into table.
How can i do this? I tried but all rows are going into flat file which i configured for error output.
Thanks,
Abhas.
October 21, 2014 at 4:07 am
Depending on the kind of error, the entire flat file might be regarded as erroneous.
For example, if a delimiter is missing, it might mess up all the rows that come after that.
Which error are you testing?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 23, 2014 at 10:00 am
I'm guessing your error rows destination is preceded by an OLE_DB destination pointing to the table?
What I've done in this kind of case is to do a two-stage insert. The first OLE_DB destination uses the Fast Load data access mode (which attempts to insert in batches.) The problem with this mode is that a single bad row in the batch causes the whole batch to be thrown away. However, it's MUCH faster than Table or view, which attempts to insert each row one at a time. So, redirect error rows from the Fast Load into another OLE_DB destination in Table or view mode, then redirect error rows to your flat file for examination. That should at least limit the error records you need to look at.
[Flat File Source]
| (green arrow)
[OLE DB Dest, Fast Load]
| (red arrow)
[OLE DB Dest, Slow Load]
| (red arrow)
[Error Flat File Destination]
As an aside, it would probably be best to select the SECOND OLE DB Destination's errorcode/errorcolumn to go into the file. It may or may not tell you what happened, but it should be more accurate than the first error.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply