BULK INSERT errors

I'm writing a .NET component that will bulk insert a file into a table.
I also have a string array inside the class whose purpose is to
capture the errors during the bulk insert process. Can this be done?If you are using the SqlBulkCopy class, an error will rollback the batch
containing the problem row and the WriteToServer operation will terminate.
In order to trap insert errors on individual rows yet still save good data,
you could use a hybrid technique in which you write a batch at a time using
WriteToServer and fall back on individual insert commands if an error is
encountered.
1) Read a batch of records from file (e.g. 1000)
2) Write records as a single batch via SqlBulkCopy.WriteToServer
3) If a SqlException is thrown, insert rows individually and trap errors
Note that you'll also need to address file parsing errors, such as an
unexpected number of fields in a CSV record.
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149281083.751510.288450@.y43g2000cwc.googlegroups.com...
> I'm writing a .NET component that will bulk insert a file into a table.
> I also have a string array inside the class whose purpose is to
> capture the errors during the bulk insert process. Can this be done?
>|||That's my biggest hurdle. I'm tied to using 1.1 Framework that doesn't
have the SqlBulkCopy class :(
I do plan on sending a batch parameter to control for that. Maybe
there's a property for the OleDbCommand that captures errors.
Dan Guzman wrote:
> If you are using the SqlBulkCopy class, an error will rollback the batch
> containing the problem row and the WriteToServer operation will terminate.
> In order to trap insert errors on individual rows yet still save good data
,
> you could use a hybrid technique in which you write a batch at a time usin
g
> WriteToServer and fall back on individual insert commands if an error is
> encountered.
> 1) Read a batch of records from file (e.g. 1000)
> 2) Write records as a single batch via SqlBulkCopy.WriteToServer
> 3) If a SqlException is thrown, insert rows individually and trap errors
> Note that you'll also need to address file parsing errors, such as an
> unexpected number of fields in a CSV record.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <tobinlim@.gmail.com> wrote in message
> news:1149281083.751510.288450@.y43g2000cwc.googlegroups.com...|||With OleDbCommand (and SqlCommand) about all you can do is try/catch. If
you send multiple statements in a batch, it's hard to identify which insert
caused the error.
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149365527.051997.242230@.u72g2000cwu.googlegroups.com...
> That's my biggest hurdle. I'm tied to using 1.1 Framework that doesn't
> have the SqlBulkCopy class :(
> I do plan on sending a batch parameter to control for that. Maybe
> there's a property for the OleDbCommand that captures errors.
> Dan Guzman wrote:
>|||What I meant is my batchsize is something that gets passed on to a BULK
INSERT statement: "BATCHSIZE = " + intBatchSize + ", ..."
Anyway, if what you're saying is true, I might have to force the
batchsize to 1 and bulk insert one row at a time and try to catch
errors.
Is this what you're saying?
Dan Guzman wrote:
> With OleDbCommand (and SqlCommand) about all you can do is try/catch. If
> you send multiple statements in a batch, it's hard to identify which inser
t
> caused the error.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <tobinlim@.gmail.com> wrote in message
> news:1149365527.051997.242230@.u72g2000cwu.googlegroups.com...|||Let me try to clarify. Some errors, like a primary key violation, will
abort the entire BULK INSERT batch. You'll need to resort to individual
inserts to identify the problem data and persist the good records. I
wouldn't recommend BULK INSERT with batch size of 1 because BULK INSERT is
optimized for large volumes of data. A batch size 1 will be much slower
than a regular inserts and defeat the purpose of BULK INSERT.
Other errors, such as a data type conversion error, will err the problem
record but not the batch. This will still throw a SqlException and you can
iterate over the Errors collection to get the details of the individual
errors. Unfortunately, it's not easy to programmatically distinguish
between errors that abort the BULK INSERT batch and those that err
individual records.
If your data is usually clean and you want the performance of BULK INSERT,
you might consider a modified version of the approach I suggested earlier:
1) BEGIN TRAN
2) BULK INSERT entire file
3) If no errors, COMMIT
4) If errors, ROLLBACK and use individual inserts to identify problem data
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149512589.648528.145350@.g10g2000cwb.googlegroups.com...
> What I meant is my batchsize is something that gets passed on to a BULK
> INSERT statement: "BATCHSIZE = " + intBatchSize + ", ..."
> Anyway, if what you're saying is true, I might have to force the
> batchsize to 1 and bulk insert one row at a time and try to catch
> errors.
> Is this what you're saying?
> Dan Guzman wrote:
>|||Thanks. I discovered the SqlException object in .NET. Very useful.
It's pretty much what I've been looking for. Now I noticed that if
multiple errors occur within a row insert, SqlException captures the
first error and moves on. I don't necessarily need to have all the
errors for each row. Just want to understand the nature of BULK INSERT
errors.
Dan Guzman wrote:
> Let me try to clarify. Some errors, like a primary key violation, will
> abort the entire BULK INSERT batch. You'll need to resort to individual
> inserts to identify the problem data and persist the good records. I
> wouldn't recommend BULK INSERT with batch size of 1 because BULK INSERT is
> optimized for large volumes of data. A batch size 1 will be much slower
> than a regular inserts and defeat the purpose of BULK INSERT.
> Other errors, such as a data type conversion error, will err the problem
> record but not the batch. This will still throw a SqlException and you ca
n
> iterate over the Errors collection to get the details of the individual
> errors. Unfortunately, it's not easy to programmatically distinguish
> between errors that abort the BULK INSERT batch and those that err
> individual records.
> If your data is usually clean and you want the performance of BULK INSERT,
> you might consider a modified version of the approach I suggested earlier:
> 1) BEGIN TRAN
> 2) BULK INSERT entire file
> 3) If no errors, COMMIT
> 4) If errors, ROLLBACK and use individual inserts to identify problem data
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <tobinlim@.gmail.com> wrote in message
> news:1149512589.648528.145350@.g10g2000cwb.googlegroups.com...|||> Thanks. I discovered the SqlException object in .NET. Very useful.
> It's pretty much what I've been looking for. Now I noticed that if
> multiple errors occur within a row insert, SqlException captures the
> first error and moves on.
Have you looked at SqlException.Errors? This contains a collection of
SqlError objects and contains details of row-level errors in my test.
However, as I mentioned earlier, you might find it problematic to
distinguish between errors that roll back the entire batch and errors that
err individual records.
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149605426.090781.127220@.j55g2000cwa.googlegroups.com...
> Thanks. I discovered the SqlException object in .NET. Very useful.
> It's pretty much what I've been looking for. Now I noticed that if
> multiple errors occur within a row insert, SqlException captures the
> first error and moves on. I don't necessarily need to have all the
> errors for each row. Just want to understand the nature of BULK INSERT
> errors.
>
> Dan Guzman wrote:
>

Building Custom Component - marking all inputs

I have been building a custom component with the default GUI. I want to have all the input fields selected to be passed through to outputs without having to explicitly check each one in the GUI. Is there some method or property to set on the input to do this?

You have to map ExternalMetadataColumnID from source to destination

|||I'm not sure this is specific to SSIS. That is, you might want to look at the appropriate .Net forum for specifics on how to code the GUI for your project.|||

I would normally do this in the UI itself, but that would be a custom UI, not the standard UI you get for free, as you cannot extend that one at all.

You could do this in ReinitializeMetaData, such that if no columns are found, and you have forced RMD to build the input/output columns for the first time, when adding you also select the columns as you create them.

How you select the column, the equivalent of selecting a column in the UI, will depend on what type of component you are writing, but normally revolves around setting the usage type, or mapping input/output columns to external metadata columns.

Build Query dialog for OLEDB Source Editor

Can i extend the "Query Builder" dialog of OLEDB Source Editor for developing custom source component?

No. None of the MS component/task UI 's can be inherited or extended, you will have to write your own.