SqlBulkCopy error: The locale id ‘0’ of the source column .. and the locale id ‘1033’ of the destination column .. do not match

I know there are similar questions but I still haven’t found the answer to this problem. My setup is the following.

Source table:

Destination table:

SqlBulkCopy call:

using (var destination = new SqlConnection(builderDestinationConnectionString.ConnectionString))
{
    try
    {
        destination.Open();
    }
    catch (Exception exception)
    {
        ...
    }

    using (var bulkCopy = new SqlBulkCopy(myConnectionString))
    {
        var cancellationTokenSource = new CancellationToken();
        bulkCopy.DestinationTableName = destinationTableName;
        bulkCopy.BatchSize = batchSize ?? GlobalContext.Configuration.BulkCopyBatchSize;
        bulkCopy.BulkCopyTimeout = GlobalContext.Configuration.BulkCopyTimeout;
        bulkCopy.EnableStreaming = true;
        bulkCopy.NotifyAfter = GlobalContext.Configuration.BulkCopyNotifyLimit;
        bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler((sender, args) => myContext.Log.Info($"{args.RowsCopied} rows copied to {destinationTableName}"));

        try
        {
            await bulkCopy
                    .WriteToServerAsync(reader, cancellationTokenSource)
                    .ConfigureAwait(false);
        }
        catch (Exception exception)
        {
            ...
        }
    }
}

Working scenario:

If my reader executes the query SELECT * FROM BulkTable the sqlBulkCopy works fine.

Non-working scenario:

this logic must be generic and sometimes I am using a select query that joins different tables and therefore I don’t want an Id. Therefore I tried this query instead of SELECT *:

SELECT CONVERT([VARCHAR](50), [SomeString]) AS [SomeString]
      ,CONVERT([DATETIME], [SomeDate]) AS [SomeDate]
      ,CONVERT([INT], [SomeInt]) AS [SomeInt]
      ,CONVERT([FLOAT], [SomeFloat]) AS [SomeFloat]
      ,CONVERT([TEXT], [SomeText]) AS [SomeText]
      ,CONVERT([DECIMAL](18, 8), [SomeDecimal]) AS [SomeDecimal]
    FROM BulkTable

And the error is:

The locale id ‘0’ of the source column ‘SomeDate’ and the locale id ‘1033’ of the destination column ‘SomeString’ do not match

I am not using KeepIdentity in the SqlBulkCopy on purpose in such a way the destination table can generate its own identity, but still nothing.

I don’t want to use ColumnMappings in the BulkCopy because as mentioned this logic must remain generic to allow different sources/destinations and therefore columns.

Answer

In your error you have 2x different column names referenced in source & destination, so you have a column algnment issue when you drop the ID.

Column alignment: the columns are being mapped positionally & in your non-working scenario when you dropped the ID the data is just left shifted one column.

Leading to somestring -> id, then somedate -> somestring.

Usually it results in a type casting error, but here it appears to have detected a locale mismatch between the columns first.

You can either: drop the ID from the destination or pad a dummy int as the first column in your select (or use some other rownum function). Or just pull the ID across & ignore it (rename the destination column to avoid confusion if it may contain duplicate values due to the multiple source tables involved).