Bulk Insert problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Bulk Insert problems

Author

Topic

Jules_S
Starting Member

13 Posts

Posted - 2012-01-13 : 05:41:03

I'm building an SSIS package to import a number of csv-style data files into an SQL 2005 database. Each csv is of a different format and is loaded to a different SQL table. I've worked out the majority of the process and got it working fine, up to the point of the actual file import, which I am using the bulk insert task to accomplish.

When running the task I was getting errors, so I read up on a number of forums and it seems that there is a bug with the task in SSIS2005. The workaround that I saw posted was to use an SQL task instead, to execute T-SQL code, using the bulk insert command. Unfortunately I still had problems with this so I decided to go back to basic principles and try a simple example. I STILL can't get it working, so I'd appreciate some help working out what I've done wrong.

OK, server is SQL2005, version 9.00.5000. Collation is Latin1_General_CI_AS. For my simple example I've carried out the following:

Created a table called dummy containing one column called colA, of type varchar(50)

In Notepad, I created a text file, called dummy.txt and typed into it the string ABCDEFGHIJ, followed by Enter (carriage return?). I saved this on a local drive on the SQL server

I manually ran bcp.exe with the format option to generate an xml format file, called dummy.xml

In Management Studio I ran the following command:

bulk insert spirit.safire.dummy
from '\\<servername>\temp\dummy.txt'
with (formatfile = '\\<servername>\temp\dummy.xml');

This passes the syntax checker, but when run, I received the following error messages:

Msg 4866, Level 16, State 7, Line 1
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 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

For reference the content of the xml format file that was generated by bcp is:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="50" COLLATION="Latin1_General_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="colA" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>

This is exactly the same set of error messages I was getting when I tried my more complex examples. I have read things like there are issues with using tables with anything other than varchar(50) columns, but that doesn't seem to be causing the problem this time. I've also read things about people changing the prefix length to zero, but that seems to be when people use non-xml format files.

I don't understand enough about ANSI vs Unicode files, datafiletype or codepages to know if anything like that is causing the issue. With my simple Notepad text file, will that be Unicode or not by default? Does hitting Enter produce a standard carriage return symbol? I have tried editing my T-SQL command to include fieldterminator, rowterminator, codepage and datafiletype parameters but I'm stabbing in the dark, and nothing I did got the command working.

I'd be very grateful for any help!

Jules

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 10:02:32

where have you defined terminator character in format file? you need to define row and column terminator inside format file

see example of xml format file below

http://msdn.microsoft.com/en-us/library/ms188365.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jules_S
Starting Member

13 Posts

Posted - 2012-01-13 : 11:11:14

In short, I haven't defined anything at all in the xml file - it was created by the bcp format option. I haven't edited it at all. I wouldn't know how to edit xml - I don't really understand it at all, although I can read what is there and make sense of it.

I can see that the example given in the link includes TERMINATOR = "\t" or "\r\n" whereas my file doesn't. I presume this would have to be manually added to the file? I thought that you could either add the terminators to the file, OR specify them for the entire file in the Bulk Insert command, using "fieldterminator" and "rowterminator"? I've tried this however and couldn't get it to work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 11:35:02

yep you can even include it in bulk insert

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jules_S
Starting Member

13 Posts

Posted - 2012-01-16 : 05:06:18

But surely I shouldn't have to do that if I specify the rowterminator and fieldterminator at the whole file level in the command?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 05:30:37

where did you specify it? i cant spot in posted code anyways

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jules_S
Starting Member

13 Posts

Posted - 2012-01-16 : 05:59:54

As I mentioned in my first post, when I executed the T-SQL "bulk insert" command, I first tried it without using fieldterminator and rowterminator, then I modified the command to include those parameters, but it still didn't work. So I ended up with the following command:

bulk insert spirit.safire.dummy
from '\\<servername>\temp\dummy.txt'
with (codepage = 'RAW', datafiletype = 'char', fieldterminator = ',', formatfile = '\\<servername>\temp\dummy.xml', rowterminator = '\r\n);

I tried these in a number of combinations, including without specifying codepage and datafiletype.

I have since discovered over the weekend that if I just run the following:

bulk insert spirit.safire.dummy
from '\\<servername>\temp\dummy.txt'
with (datafiletype = 'char', fieldterminator = ',', rowterminator = '\n');

i.e. I don't specify a format file, it works! How on earth does that happen?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 06:53:24

format file is optional. if you're passing delimiter info directly no need of format file. Either of them is enough not both

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jules_S
Starting Member

13 Posts

Posted - 2012-01-16 : 12:19:25

I wish I'd known that before I started! :-) Ah well. It looks like I've got things working now, albeit having to import everything as character, because the decimal columns in the source file have a trailing sign that I'm having to manipulate before converting to a decimal column type.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:32:30

if you want to remove it why not add trailing sign also to column delimiter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources