If you need to import large CSV files in SQL Server and you want to avoid using Integration Services you can use SqlBulkCopy from .Net based application.
- Integration Services is more sensitive to regional settings
- SSIS wants configuration for every column of every csv file you want to import
- Error handling is a odd (at least for me – with less or no previous experience)
- SSIS requires at least SQL Server Standard – you have no SSIS run time in SQL Server Express Edition. Kind of an important reason.
How to use it:
Write your schema.ini file and copy it programmatically to the folder with the csv files:
Use Jet Oledb:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\samplefilelocation; Extended Properties=""Text;HDR=No;FMT=Delimited""";
Change Regional Settings (just to be sure that the dates and numbers are parsed correctly). Even If you have set those in Schema.ini the DateFormat and DecimalSeparator are not taken in consideration:private void ChangeRegionalSettings()
CultureInfo goodCulture = new CultureInfo("en-us", false);
goodCulture.NumberFormat.NumberDecimalSeparator = ".";//this is a sample code
goodCulture.DateTimeFormat.ShortDatePattern = "dd.MM.yyyy"; //this is a samplecode
Thread.CurrentThread.CurrentCulture = goodCulture;
}Open the connection to the CSV file:OleDbConnection conn = new OleDbConnection(strCSVConnString);
OleDbCommand dbCommand = new OleDbCommand(strSql, conn);
OleDbDataReader dataReader = dbCommand.ExecuteReader();Create a new Instance of SqlBulkCopy and pass the reader to the WriteToServer method:SqlBulkCopy bulk = new SqlBulkCopy(sqlConnectionString);
bulk.BatchSize = 10;//set appropriate values for large files you can try with 100 000
bulk.NotifyAfter = 10;
bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulk_SqlRowsCopied);
bulk.DestinationTableName = "DestinationTableName";
That’s it. You will be impressed by the speed this class provides. One more thing is that it will do everything to convert your date to the appropriate database format defined in your destination table definition.
If you have some questions feel free to comment below.