SQL Server Bulk Insert failing when called from .NET SqlCommand

Question

I have a stored procedure which does bulk insert on a SQL server 2005 database. When I call this stored procedure from some SQL (passing in the name of a local format file and data file) it works fine. Every time.

However, when this same stored procedure gets called from C# .NET 3.5 code using SqlCommand.ExecuteNonQuery it works intermittently.

When it fails a SqlException is generated stating "Cannot bulk load. Invalid column number in the format file "c:\bulkinsert\MyFile.fmt"

I don't think this error message is correct.

Has anyone experienced similar problems with calling bulk insert from code?

Thanks.

Answer 1

How are you doing the bulk insert? Usually, the problem (in this scenario) is whether "c:\" is the server's "c:\", or the client's "c:\".

However. from C# code, the simplest approach is to use SqlBulkCopy. This class provides direct access to bulk-insert functionality from managed code, including mappings (although I never bother with them).

If the file is something like csv / tsv / similar, then CsvReader is highly recommended. This provides the IDataReader interface that WriteToServer uses most efficiently.

Answer 2

Thanks for your reply Marc.

It refers to the clients c:\ drive.

I have managed to narrow the problem down. It has nothing todo with .NET as I can recreate the problem by just executing SQL.

When I run this is fails intermittantly. This is basically what our stored procedure in production is doing.

Declare @Sql Nvarchar(255);

SET @Sql = 'Bulk Insert TestOutputPretest From ''c:\rawdata\bulkinsert\Segment1839204.dat'' With (FormatFile=''c:\bulkinsert\TestOutputPretest_FormatFile_V1_1.fmt'')'

Exec sp_ExecuteSql @Sql;

Interestingly when I execute some SQL identical to the above piece but without the use of SP_EXECUTESQL it works 100% of the time.

Bulk Insert TestOutputPretest From 'c:\rawdata\bulkinsert\Segment1839204.dat' With (FormatFile='c:\bulkinsert\TestOutputPretest_FormatFile_V1_1.fmt')

Weird or what! Perhaps as you mention the best course is to use SqlBulkCopy.

Answer 3

I think the problem was todo with the format file. I am no longer using the format file and it seems to work 100% of the time now. I specify field and row terminators in the SQL instead.

Declare @Sql Nvarchar(2000);

SET @Sql = 'Bulk Insert TestOutputPretest From ''c:\rawdata\bulkinsert\Segment1839204.dat'' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR = ''\n'' )'

Exec sp_ExecuteSql @Sql;