Load of generic .csv files into SQL Table using BulkCopy

Question

I am trying to create a generic module that will load .csv files into SQL tables. The SQL tables are already created and their names, and the name of the file, will be passed as parameters. This what I have so far...

public void Main()  
    {  
        var mFilepath = Dts.Variables["InputFile"].Value.ToString();  
        var mSQLTable = "[Staging].[tblLoadBUF]";  
  
        try  
        {  
            DataTable dt = new DataTable();  
  
            string contents = File.ReadAllText(mFilepath, System.Text.Encoding.GetEncoding(1252));  
  
            TextFieldParser parser = new TextFieldParser(new StringReader(contents));  
  
            parser.HasFieldsEnclosedInQuotes = true;  
            parser.SetDelimiters(",");  
  
            string[] fields;  
  
            while (!parser.EndOfData)  
            {  
                fields = parser.ReadFields();  
  
                if (dt.Columns.Count == 0)  
                {  
                    foreach (string field in fields)  
                    {  
                        dt.Columns.Add(new DataColumn(string.IsNullOrWhiteSpace(field.Trim('\"')) ? null : field.Trim('\"'), typeof(string)));  
                    }  
                }  
                else  
                {  
                    dt.Rows.Add(fields.Select(item => string.IsNullOrWhiteSpace(item.Trim('\"')) ? null : item.Trim('\"')).ToArray());  
                }  
  
  
            }  
  
            parser.Close();  
  
  
            string connectionString = Dts.Connections["OLEDB_CONN"].ConnectionString;      
            connectionString = connectionString.Trim(';');   
            var connStrDictionary = connectionString.Split(';').Select(x => x.Split('=')).ToDictionary(x => x[0], x => x[1]);       
  
            connectionString = "Data Source=" + connStrDictionary["Data Source"] + ";Initial Catalog=" + connStrDictionary["Initial Catalog"] + ";Integrated Security=" + connStrDictionary["Integrated Security"];   
            using (SqlConnection con = new SqlConnection(connectionString))  
            {  
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))  
                {  
                    sqlBulkCopy.DestinationTableName = mSQLTable;  
                    sqlBulkCopy.ColumnMappings.Clear();  
                    con.Open();  
  
                    foreach (var column in dt.Columns)  
                    {  
                        sqlBulkCopy.ColumnMappings.Add(column.ToString(), column.ToString());  
                    }  
  
                    sqlBulkCopy.WriteToServer(dt);  
                    con.Close();  
                }  
            }                      
              
  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
  
        }  
        catch (Exception ex)  
        {  
            Dts.Events.FireError(0, "Something went wrong ", ex.ToString(), string.Empty, 0);  
            Dts.TaskResult = (int)ScriptResults.Failure;  
        }  
    }  

This is not working. Error received :
System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or destination.

I did verified the input file column names as I know that they are case sensitive, and they are all the same. However, I really would like to get a turnaround to verify the source and destination columns, since the input files will be sent from many sources and we don't have any control on how they will be created... with uppercase or lowercase variable names.

Can somebody help me solve this ? Pease note that English is not my primary language, so just let me know if this is not clear enough. And thanks in advance for you help guys :-)

Mylene

Answer 1

Unsure how you verified column names so you might try using the following by iterating DataTable column names directly from the actual column names take from the following code. Make sure to adjust the connection string to match your connection.

using System.Collections.Generic;  
using System.Data;  
using System.Data.SqlClient;  
  
namespace YourNamespaceGoesHere  
{  
    public class SqlServerUtilities  
    {  
        /// <summary>  
        /// Change this to your database server and catalog  
        /// </summary>  
        public static string ConnectionString =>  
            "Data Source=.\\sqlexpress;Initial Catalog=Northwind2020;Integrated Security=True";  
  
        /// <summary>  
        /// Pass in an existing table name to get column names  
        /// </summary>  
        /// <param name="tableName">table name to get columns for</param>  
        /// <returns>list of column names</returns>  
        public static List<string> ColumnNameForTable(string tableName)  
        {  
            var columnNames = new List<string>();  
            using (var cn = new SqlConnection() {ConnectionString = ConnectionString})  
            {  
                using (var cmd = new SqlCommand() {Connection = cn})  
                {  
                    cmd.CommandText = GetColumnNamesStatement;  
                      
                    cmd.Parameters.Add("@TableName", SqlDbType.NChar)  
                        .Value = tableName;  
  
                    cn.Open();  
  
                    var reader = cmd.ExecuteReader();  
  
                    while (reader.Read())  
                    {  
                        columnNames.Add(reader.GetString(0));  
                    }  
                }  
            }  
  
            return columnNames;  
  
        }  
  
        public static string GetColumnNamesStatement => @"  
        SELECT COLUMN_NAME   
        FROM INFORMATION_SCHEMA.COLUMNS  
        WHERE TABLE_NAME = @TableName  
        ORDER BY COLUMN_NAME";  
    }  
}