Before putting into the database, convert String to bit.

String conversion is refused by SqlBulkCopy. INT NULL is empty.

Recently I've been tasked with creating an automated ETL process that pumps the data into tables based on the flat file name by reading a master mapping file. I've decided to go with SqlBulkCopy and everything seemed to be fine. IDataReader interface was implemented to read flat-files, SQL Server's meta-data provided with number of columns for a on...

SQL Server 2008 database: insert relational(normalized) data tables quickly

I'm trying to find a better and faster way to insert pretty massive amount of data(~50K rows) than the Linq that I'm using now. The data I'm trying to write to a local db is in a list of ORM mapped data serialized and received from WCF. I'm keen on using SqlBulkCopy, but the problem is that the tables are normalized and are actually a sequence or i...

How can I put data into a table in a non-default database schema using SqlBulkCopy?

I need to insert data into a table in a schema named ...Staging... using ...SqlBulkCopy.......It appears the API only allows you to set the target table name by using the ...DestinationTableName... property....How do I accomplish this? Is it possible?

When doing bulk inserts, how can you automatically truncate strings?

I want to insert many rows (constructed from Entity Framework objects) to SQL Server. The problem is, some of string properties have length exceeded length of column in database, which causes an exception, and then all of rows will unable to insert into database. ...So I wonder that if there is a way to tell SqlBulkCopy to automatically truncate an...

Inserting unique id for file uploaded to database using SqlBulkCopy

There is a table with the following columns:...ProdID Specification Value ...I've got a csv file with a schema that only has the last two columns - ...Specification... and ...Value.... In other words they are my csv headers. Now I need to associate the values of this file against one unique id....The significance of that id is that it is a referenc...

SQLBulkcopy - Header of datatable getting inserted in the database table

The header of the datatable is getting inserted as a record in the table. I am incrementing the row number but still the header gets inserted. Could someone tell me what the issue is ? If you see the code below the I am doing rows.MoveNext.... XSSFWorkbook xssfwb; using (FileStream file = new FileStream(excelPath, FileMode.Open, File...

string filed is truncated on SqlBulkCopy insert

I use SqlBulkCopy to insert excel data to table....One of the fields is ntext on the destination table but when the data is inserted the data is truncated after 255 chars....using (OleDbConnection conExcel = new OleDbConnection(connstring)) { DL_ID = library.Save(); conExcel.Open(); ...

sqlbulkcopy - When converting string to DateTime

I am using this code to insert a csv file to my database:... private void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData) { using (SqlConnection dbConnection = new SqlConnection(ConnectionString)) { dbConnection.Open(); using (SqlBulkCopy s = new SqlBulkCopy(dbConnection)) { ...

LINQ: Convert any empty string to Null

I have a list populated with massive amount of data and over hundred columns. I will have to convert the entire list to DataTable and use ...SQLBulkCopy... to batch insert all data into the database. ...Everything is okay except that some of the fields could be empty. And for some reason, some of the empty fields causes some formatting exception af...

SqlBulkCopy with ObjectReader - Failed to convert parameter value from a String to a Int32

I am using SqlBulkCopy (.NET) with ObjectReader (FastMember) to perform an import from XML based file. I have added the proper column mappings. ...At certain instances I get an error: Failed to convert parameter value from a String to a Int32. ...I'd like to understand how to 1. Trace the actual table column which has failed 2. Get the "current" o...