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]";
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;
}
}
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.
Here is my SQL Table:
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
And my .csv file contains values of 1 and 0 in column [Do_Not_Contact_Flag].
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
