7

I have previously used the SQLBulkCopy class to load data into a MS SQL Server db. The results were very good, and worked exactly as I intended it to.

Now, I'm trying to use a script task in SSIS to bulk load data into a MySQL (5.5.8) database using either an ODBC or ADO.NET connection (recommend?).

The columns in my dataset correspond with the columns of the MySQL table. What is the best way to do a bulk insert of a dataset into a MySQL database?

yamn2
  • 71
  • 1
  • 1
  • 4
  • 2
    If your data set is in a text format, you don't need to write a program, just write a `LOAD DATA INFILE` query and MySQL will load the file itself. – Dan Grossman Feb 03 '11 at 07:19
  • Dan, thanks for the suggestion I will be scheduling the SSIS to load a series of text files, and need to check some file content before I decide to insert the data. This is why I'm doing this programatically. – yamn2 Feb 03 '11 at 08:26

1 Answers1

14

You can use the MySqlBulkLoader shipped with the MySQL Connector for .NET:

var bl = new MySqlBulkLoader(connection);
bl.TableName = "mytable";
bl.FieldTerminator = ",";
bl.LineTerminator = "\r\n";
bl.FileName = "myfileformytable.csv";
bl.NumberOfLinesToSkip = 1;
var inserted = bl.Load();
Debug.Print(inserted + " rows inserted.");
Erwin Mayer
  • 18,076
  • 9
  • 88
  • 126
  • I have tried same code but its not working inserted always remains zero . – rahularyansharma Sep 03 '13 at 19:16
  • @rahularyansharma make sure your installation of MySQL supports LOAD_DATA_INFILE (http://dev.mysql.com/doc/refman/5.1/en/connector-net-programming-bulk-loader.html) as MySQLBulkLoader is just a wrapper around it. – Erwin Mayer Sep 04 '13 at 08:07
  • 1
    ok, then make sure all parameters are specified correctly, as LOAD_DATA_INFILE may assume parameters which must be explicitly specified with MySqlBulkLoader. – Erwin Mayer Sep 04 '13 at 08:41
  • now its running OK but only uploading a single record, May be issue due to my csv becuase when i open csv in notepad then there is no column . – rahularyansharma Sep 04 '13 at 08:44
  • Looks like a LineTerminator issue, it can arrive if your CSV file was generated on another OS. You may try with \n or \r alone instead of \r\n. Also check that the FieldTerminator is correct (in my code I use a comma). – Erwin Mayer Sep 04 '13 at 09:04
  • I have a single column only and i have tried with \r and \n and with both but its still inserting single row and that single row genrating only identity column value no data in columns . – rahularyansharma Sep 04 '13 at 16:28
  • 1
    You should try different CSV files with different data (including the simplest possible). You can also use Notepad++ to show all characters to make sure your file is properly formatted (notepad.exe is very basic). – Erwin Mayer Sep 05 '13 at 06:20