[dba-SQLServer] Bulk insert puzzle

Just a WAG, but do you have a header line in the file containing the field 
names.

That would cause a problem reading Row 1, Column 1 with FirstLine = 1 
and the first column being a BIGINT.



On 15 Sep 2008 at 15:09, Arthur Fuller wrote:

> 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:
> 
> <sql>
> 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
> ) ON [PRIMARY]
> GO
> 
> </sql>
> 
> Here is the next part of the puzzle:
> 
> <sql>
> 
> PRINT GETDATE()
> BULK INSERT BPOD.dbo.BPODMessage_Staging
> FROM 'C:\Documents and Settings\afuller\My Documents\BPOD\BPOD_Message.txt'
> WITH (FIRSTROW=1, BATCHSIZE = 1000, FIELDTERMINATOR=',', ROWTERMINATOR='\n')
> PRINT GETDATE()
> 
> </sql>
> 
> This results in these error messages:
> 
> <sql>
> 
> 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
> correctly.
> 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)".
> 
> </sql>
> 
> A line of the data I'm attempting to import looks like this:
> 
> <data>
> 
> 678605358674,Summary.Message,Summary.Last.Bid.Time,20080703
> 11:56:21.000,2008-07-03 08:54:31.820000000,000000001ABA0AC1
> 
> </data>
> 
> 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?
> 
> TIA,
> 
> Arthur
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>