SSIS C# SQLBulkCopy error : Failed to convert parameter value from a String to a Boolean.

Question

Hello guys !

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

I get the following error message at execution:
System.InvalidOperationException: The given value of type String from the data source cannot be converted to type bit of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a Boolean. ---> System.FormatException: String was not recognized as a valid Boolean.

Can somebody help me fix this ? I understand the error message, but I find it strange that it doesn't complain about decimal or integer values.

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 :-)

Mylene

Answer 1

what are the actual data values?

sqlserver has bit rather than Boolean. string to bit supports:

"0" => 0
"FALSE" => 0
"1" => 1
"TRUE" => 1

a common bulk loading process is to use all nullable string column in the load table. then run a proc to validate the data in the columns. then move the load data to actual table.