SQL Bulk Copy: Insert failing but not throwing any errors.

Question

_{Wednesday, November 12, 2014 8:11 AM}

I have a few processes that process LOTS of data by using SQL Bulk Copy. We are talking like 200,000 rows or more. Most of the time these processes run no problem and take a minute or two to complete. 

Once in a while they will fail but no errors will be stored, emailed, or placed in the various error tracking things I have set up. What we see is the count of rows in the table being incorrect as if the bulk copy just stopped mid way through and then my process continues as if nothing bad happened.

Here is my bulk copy code for one of them.

Dim bc As SqlBulkCopy = New SqlBulkCopy(baswarecon)

                    bc.DestinationTableName = "supplier"
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("vendor_group", "comp_no"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("supplier_num", "supplier_num"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("supplier_name", "supplier_name"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("supplier_1", "supplier_name2"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("supplier_1", "supplier_1"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("supplier_2", "supplier_2"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("REMIT", "supplier_3"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("supplier_4", "supplier_4"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("supplier_5", "supplier_5"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("TERMS", "supplier_6"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("supplier_3", "supplier_7"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("supplier_8", "supplier_8"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("paymethod", "supplier_9"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("income_code", "supplier_10"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("sep_chk_flag", "supplier_11"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("vendor_sname", "supplier_account_1"))
                    bc.ColumnMappings.Add(New SqlBulkCopyColumnMapping("ven_class", "supplier_12"))
                    bc.BulkCopyTimeout = 90000
                    bc.BatchSize = 5000
                    bc.WriteToServer(ds.Tables(0))
                    bc.Close()

Data is being pulled from an iseries machine into a dataset and we use that dataset to process the copy. Data from the iSeries is fine so the error comes in during the copy.

I can take the same process and do it the long way and insert one line at a time and it won't fail there either (as a test, it takes AGES to process 200,000 rows that way)

Sadly these processes are in a timed windows service and watching them isn't really feasible. I can install said service on my local machine and run them without errors either. It is a total fluke when it happens. Talking like once a month if that from something that has been running for years.

So is there something I missing in terms of troubleshooting a SQL Bulk Copy?

All replies (5)

_{Thursday, November 13, 2014 9:29 AM ✅Answered}

I can only assume you didn't read it either because if I wasn't opening the connection (which just isn't shown) then it wouldn't have worked at all and my post obviously states that it WORKS almost all the time....

Please close this thread since no one wants to actually read it...

_{Wednesday, November 12, 2014 8:20 AM}

1. Increase the batch size to 50000

2. Add try catch and log the error to file

3. see to that all the columns are present in table in dataset and mappings are correct

_{Wednesday, November 12, 2014 8:28 AM}

i have it wrapped in a try catch a long with writing to a text file throughout trying to capture what happens. Also the whole process will dump into the event log if any part fails and it doesn't do that. 

Again, it all just shows up as if it was A'Ok.

You didn't really read my post did you?

_{Wednesday, November 12, 2014 9:20 AM}

If everything is ok? why it is failing and why are you not seeing all the records SQL table from dataset.

I read your post and am also using the bulk copy in windows service.

_{Thursday, November 13, 2014 5:16 AM}

Hi,

According to your description, I think you need to make sure that you have open th connectionstring 'baswarecon'. The same time I suggest you trying to create a small demo about inserting simple data into DataBase, if this is ok, then this connectionstring is correct.

There is a demo about SQL Bulk copy method to insert large amount of data to the sql database, please refer to the link below:

http://www.codeproject.com/Tips/309564/SQL-Bulk-copy-method-to-insert-large-amount-of-dat

Hope it's useful for you.

Best Regards,

Michelle Ge