Tuesday, June 3, 2008

SqlBulkCopy vs SSIS Bulk Insert

I recently wrote a program to move data from a CSV file into a SQL Server table using SqlBulkCopy.
The CSV file had data like
0,0,35.6,-122.5,1,
0,1,35.61188,-122.4438,1,
0,2,35.61979,-122.4062,1,
.
.

Below are some of my observations.

Trailing Delimiter Handling
With SqlBulkCopy, the data was successfully moved to SQL Server.

But when I used the SSIS Bulk Insert Task, the following error was reported

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 5
.
.

The fix for this was to remove the trailing ',' character at the end of each line in the CSV file as shown below and then perform SSIS Bulk Insert.
0,0,35.6,-122.5,1
0,1,35.61188,-122.4438,1
0,2,35.61979,-122.4062,1
.
.

Speed
I did not observe any significant difference in the data load speed of SqlBulkCopy & SSIS Bulk Insert task. I will post performance numbers at some later time.

No comments:

What is success?

The journey of life takes us through varied experiences like landing an admission at a prestigious college, earning a degree, getting hired,...