使用BulkCopy将通用.CSV文件的加载到SQL表中

Question

我正在尝试创建一个通用模块，该模块将.CSV文件加载到SQL表中。 SQL表已创建，其名称以及文件的名称将作为参数传递。到目前为止，这是我所拥有的...

    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 will contain unsupported keywords like 'provider'
            connectionString = connectionString.Trim(';'); //  Remove the trailing semicolon so that when we perform the split in the following line, there are no index errors.
            var connStrDictionary = connectionString.Split(';').Select(x => x.Split('=')).ToDictionary(x => x[0], x => x[1]);     //   Here we get each value-pair from connection string by splitting by ';', then splitting each element by '=' and adding the pair to a Dictionary.

            connectionString = "Data Source=" + connStrDictionary["Data Source"] + ";Initial Catalog=" + connStrDictionary["Initial Catalog"] + ";Integrated Security=" + connStrDictionary["Integrated Security"]; //  Build the actual connection string to be used.
            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;
        }
    }

这还没有起作用。收到的错误：
System.InvalidoperationException：给定的列映射与源或目标中的任何列不匹配。

我确实验证了输入文件列的名称，因为我知道它们对案例敏感，并且它们都是相同的。但是，我真的想获得一个周转来验证源和目标列，因为输入文件将从许多源发送，我们对它们将如何创建...使用大写或小写的变量有任何控制权。名称。

有人可以帮我解决这个问题吗？请注意，英语不是我的主要语言，所以让我知道这是否还不够清晰。预先感谢您的帮助:-)

Mylene

Answer 1

我终于找到了一种方法！您必须查询目标表以获取列列表。如果可以帮助某人，这是代码...

public void Main()
    {
        var mFilepath = Dts.Variables["InputFile"].Value.ToString();
        var mSQLTable = "[Staging].[tblLoadBUF]";
        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"];


        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();

            var columnNames = new List<string>();
            using (var cn = new SqlConnection() { ConnectionString = connectionString })
            {
                using (var cmd = new SqlCommand() { Connection = cn })
                {
                    cmd.CommandText = Dts.Variables["StagingTableGetColumnsScript"].Value.ToString();

                    cn.Open();

                    var reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        columnNames.Add(reader.GetString(0));
                    }
                    cn.Close();
                }
            }


            using (SqlConnection con = new SqlConnection(connectionString))
            {                   

                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    sqlBulkCopy.DestinationTableName = mSQLTable;
                    sqlBulkCopy.ColumnMappings.Clear();
                    con.Open();

                    foreach (var column in columnNames)
                    {
                        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;
        }
    }