[dba-SQLServer] Bulk insert puzzle

Arthur Fuller fuller.artful at gmail.com
Mon Sep 15 13:09:34 CDT 2008

I have a peculiar puzzle here. I will soon embark on a new project in which
I'll be using SSIS to grab millions of rows coming in daily. A c++ component
will be reading a stream and writing the data. Our original concept was that
the c++ component would do inserts into a staging table and occasionally
flush that table, moving the data into the real database. Now that we see
the speed and quantity of the incoming data, we are not so sure we will be
able to keep up. So I have been investigating the possibility of using Bulk
Insert from many text files, so the process would be something along the
lines of:

1. c++ writes a new file every 10k rows.
2. bulk insert kicks in and inserts that entire file into a staging table.
3. destroy the file just inserted.
4. move the data from the staging table to the real database.
5. Repeat until the last text file has been deleted.

So the algorithm is fairly clear (although as always subject to adjustment
as we move into development and production).

I have some sample data of the type we will be receiving. This morning I
created an SSIS package using the Import/Export wizard to dump that data to
a csv file. Fine, so far. Then I created a bulk insert script. First here is
the table structure:

CREATE TABLE [dbo].[BPODMessage_Staging](
 [SecurityIDBSID] [bigint] NOT NULL,
 [MessageName] [varchar](200) NOT NULL,
 [FieldName] [varchar](200) NOT NULL,
 [DataValue] [varchar](50) NULL,
 [TimeOfUpdate] [datetime] NOT NULL,
 [Ts] [timestamp] NOT NULL


Here is the next part of the puzzle:


BULK INSERT BPOD.dbo.BPODMessage_Staging
FROM 'C:\Documents and Settings\afuller\My Documents\BPOD\BPOD_Message.txt'


This results in these error messages:


Msg 4866, Level 16, State 1, Line 2
The bulk load failed. The column is too long in the data file for row 1,
column 1. Verify that the field terminator and row terminator are specified
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The
provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


A line of the data I'm attempting to import looks like this:


11:56:21.000,2008-07-03 08:54:31.820000000,000000001ABA0AC1


Given that the staging table was created by scripting the final table to a
Create To | New Query window and then created, how could data that came from
an identical structure now suddenly not fit?

Any ideas?



More information about the dba-SQLServer mailing list