SQL Bulk Copy by mapping columns dynamically does not work but works manually

89 views Asked by At

I am trying to do a columnMappings for a bulk insert but i cant get the Columnmapping to work in a for loop but when i do it manually it works just fine.

I get this error when trying a dynamic ColumnMapping even though it adds the ColumnMapping to the "objbulk" with the same columns name as columns in the manual ColumnMapping:

Exception thrown: 'System.InvalidOperationException' in System.Data.SqlClient.dll

using (var objbulk = new SqlBulkCopy(connConfig, SqlBulkCopyOptions.Default, objTrans))
{                                 
    DataColumn colDateTime = new DataColumn("UpdateTimestamp");
    dt.Columns.Add(colDateTime);
    foreach (DataRow row in dt.Rows)
    {
        row.SetField("UpdateTimestamp", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss.fffffff"));
    }
    objbulk.DestinationTableName = tableName;
    foreach (DataColumn column in dt.Columns)
    {
        objbulk.ColumnMappings.Add(column.ColumnName, column.ColumnName);
    }
                                       
    /*         objbulk.AutoMapColumns(dt);*/
    /* objbulk.ColumnMappings.Add("ColumnA", "ColumnA");
     objbulk.ColumnMappings.Add("ColumnB", "ColumnB");
     objbulk.ColumnMappings.Add("ColumnC", "ColumnC");
     objbulk.ColumnMappings.Add("ColumnD", "ColumnD");
     objbulk.ColumnMappings.Add("UpdateTimestamp", "UpdateTimestamp");*/
    objbulk.WriteToServer(dt);                              
}
objTrans.Commit();
return 1;

what am I doing wrong? its so strange that its basically the same thing. Could anyone tell me what i am missing here?

Thanks!

0

There are 0 answers