SSIS C＃SQLBULKCOPY .CSV文件错误：无法将参数值从字符串转换为布尔值。字符串未公认为有效的布尔值

Question

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

 public void Main()
    {
        var mFilepath = Dts.Variables["InputFile"].Value.ToString();
        var mSQLTable = "[Staging].[tblLoadBUF]"; 
        Dts.Variables["StagingTableGetColumnsScript"].Value = 
        "IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tblLoadBUF') " +   
          "BEGIN; " +
            "SELECT COLUMN_NAME " +
            "FROM INFORMATION_SCHEMA.COLUMNS " +
            "WHERE TABLE_NAME = 'tblLoadBUF'; " +
          "END; "; 

      

        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;
        }
    }

我在执行时收到以下错误消息： System.InvalidoperationException：从数据源类型字符串的给定值不能转换为指定目标列的类型位。 ---＆gt; System.FormateXception：无法将参数值从字符串转换为布尔值。 ---＆gt; System.FormateXception：字符串未被认为是有效的布尔值。

有人可以帮我解决这个问题吗？我理解错误消息，但我觉得很奇怪，因为它不会抱怨十进制或整数值。

这是我的sql表：

CREATE TABLE [Staging].[tblLoadBUF](
     [Bg_SN] [NVARCHAR](12) NOT NULL,
     [Bg_Type] [NVARCHAR](7) NOT NULL,
     [Bg_Expected_BUs] [NUMERIC](4, 0) NOT NULL,
     [Bg_Validity_Likelihood] [DECIMAL](5, 4) NOT NULL,
     [Bg_Mixed_Usage] [NUMERIC](1, 0) NOT NULL,
     [Bg_Status] [NVARCHAR](1) NOT NULL,
     [BU_SN] [NVARCHAR](12) NOT NULL,
     [BU_Residential_Occup_Likelihood] [DECIMAL](5, 4) NOT NULL,
     [BU_Last_Res_Confirmation] [DATE] NULL,
     [BU_Last_NRes_Usage] [NVARCHAR](7) NULL,
     [BU_Last_NRes_Confirmation] [DATE] NULL,
     [BU_Validity_Likelihood] [DECIMAL](5, 4) NOT NULL,
     [BU_Status] [NVARCHAR](1) NOT NULL,
     [BU_Mailing_Address_Availability] [NUMERIC](1, 0) NOT NULL,
     [BU_Mailing_Address_Likelihood] [DECIMAL](5, 4) NULL,
     [BU_Usage] [NUMERIC](1, 0) NOT NULL,
     [BU_Co_SN] [NVARCHAR](12) NULL,
     [Co_Type] [NVARCHAR](5) NULL,
     [Co_Validity_Likelihood] [DECIMAL](5, 4) NULL,
     [Co_Status] [NVARCHAR](1) NULL,
     [TN_LTotal] [INT] NOT NULL,
     [TN_CTotal] [INT] NOT NULL,
     [TN_OTotal] [INT] NOT NULL,
     [TN_Total] [INT] NOT NULL,
     [EA_Total] [INT] NOT NULL,
     [BB_UID] [NUMERIC](10, 0) NULL,
     [BB_BPIR] [NVARCHAR](4) NOT NULL,
     [CUID] [NVARCHAR](8) NULL,
     [COLB] [NVARCHAR](10) NULL,
     [DAID] [NVARCHAR](8) NULL,
     [DISB] [NVARCHAR](11) NULL,
     [CSD_Name] [NVARCHAR](100) NULL,
     [CSD_UID] [NVARCHAR](7) NULL,
     [CSD_Type] [NVARCHAR](3) NULL,
     [SAC_Code] [NVARCHAR](3) NULL,
     [PC_CUID] [NVARCHAR](8) NULL,
     [PC_DAID] [NVARCHAR](8) NULL,
     [PC_CSD_UID] [NVARCHAR](7) NULL,
     [PC_CSD_Type] [NVARCHAR](3) NULL,
     [PC_SAC_Code] [NVARCHAR](3) NULL,
     [LFS_UID] [NVARCHAR](13) NULL,
     [ER_UID] [NVARCHAR](4) NULL,
     [HR_UID] [NVARCHAR](4) NULL,
     [PRCODE] [NVARCHAR](2) NOT NULL,
     [BU_CPC_Postal_Code] [NVARCHAR](6) NULL,
     [Bg_Latitude] [DECIMAL](9, 6) NULL,
     [Bg_Longitude] [DECIMAL](11, 6) NULL,
     [Bg_Coordinate_Type] [NVARCHAR](1) NOT NULL,
     [AR_UID] [NVARCHAR](10) NULL,
     [Frame_ID] [NVARCHAR](8) NULL,
     [Do_Not_Contact_Flag] [BIT]  NULL,
 PRIMARY KEY CLUSTERED 
 (
     [BU_SN] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
    
 GO

我的.csv文件包含列中的1和0的值[do_not_contact_flag]。

请注意，英语不是我的主要语言，所以让我知道这是否还不够清晰。预先感谢您的帮助:-)

Mylene

Answer 1

我找到了解决方案！！

我已经修改了我的登台表列[do_not_contact_flag]为smallint，并将转换添加到我的处理proc中的位置，然后再加载在最终的SQL表中。