Bulk Insert causing duplicates in target table

[gnanny]

We use ETL tool(C# based) which uses Bulkcopy method to do bulk insert to target table.

Recently we are facing  2 Issues in our daily loads

1. Target table with no primary key 

- the load returns success but it inserts 5x times of the same record. It loads same records 5 times.

2. Target table with Primary Key 

- The load returns failure with primary key constraint  but it inserts the rows into target table.

DBA Team is unable to find anything in there logs ,So I am confused why this error occurs.

This occurs daily  in 10% of the jobs and once you rerun after 2-3 hrs the same job finishes successfully without above mentioned issues.

Please suggest how can I debug this issue.

[#2]

---

1. Target table with no primary key 

---

that's already the main failure.

It loads same records 5 times.

You didn't provide any detail about your ETL process/tool, source data etc., so how could we guess?

DBA Team is unable to find anything in there logs

Of course not, why should SQL Server log simple DML operations on it's own?

You have to dig depper into it on your own and provide much more informations for us.

[#3]

@gnanny 

Do you mean the .NET SqlBulkCopy class (referenced below), or some method from another class?

• SqlBulkCopy Class (Microsoft.Data.SqlClient) | Microsoft Learn

If it's the SqlBulkCopy class that you're talking about then you're looking at the wrong end: it's not your destination table(s) at fault; it's the source table/query used to generate the data.

The target table is akin to a dumb terminal that receives whatever is contained in the source table or query, so the problem is definitely not at that end - or perhaps I should clarify and say that it's the SqlBulkCopy.WriteToServer() is not the cause of the fault (since if your package contains additional code after the bulk copy's WriteToServer() then I suppose anything is possible.)

Cheers,

Lain

[#4]

Hi @gnanny 

You probably familiar with the sentence "the total is more than the sum of its parts".

You present two behaviors which we should think about together and not solve each separately.

The combination of "no primary key => loads same records 5 times" and the fact that "when primary key => failure with primary key constraint" clearly is a result of attempting to INSERT the data multiple times.  The issue is with your application code as it seems.

using Bulk Insert in the correct way does not INSERT the same row more than once - not if it has PK or not.

Seems like your app run the insert 5 times so if the there is no PK then it INERT the 5 tims and if there is PK then it cannot insert the second time.

To help more than this we will need to see the entire code of the application that relevant to this. In anyway, this means that the issue is not in the SQL Server side and should be in a different forum under .Net or C#.