Duplicate key issues with SQL databases - incorrect collation

Some data extractions may run into duplicate key problems, even if all key fields are exported.

For example, a table may have a primary key including a GUID character column, and the data for this column is case-sensitive.

Attempting to export this to a MS SQL database with a case-insensitive collation, the VirtDB DDE creates the temp table with columns using the default collation of the database. If it is case-insensitive, then can cause an error on the PRIMARY KEY constraint due to a duplicated GUID key.

You can use setting PrimaryKeyCollation in the appsettings.json of the Data Distribution Engine in order to use a specific (case-sensitive) collation to avoid duplicates arising from this:

 

The DDE must be restarted after making this change.