Re: Bulk insert via SQL DTS

From: Teretsky (Teretsky_at_gmail.com)
Date: 09/21/04 

Date: 21 Sep 2004 05:56:41 -0700

Pete,
Thanks for the tip on the Notepad2. The files I'm using are created
from our own SQL server and have no line endings at all. The txt files
are created using DTS's Text File destination task and are populated
from the tables directly using '~' for a column delim and #@#@# for a
row delim. I've actually gotten passed the errors by just using the
BULK INSERT command from within a stored proc. A new issue has
emerged:

Invalid column terminator for column number 1 row 1

Here's the format file I'm using:

8.0
4
1 SQLINT 0 4 '~' 1 currencyID SQL_Latin1_General_Cp437_Bin
2 SQLCHAR 0 50 '~' 2 currencyName SQL_Latin1_General_Cp850_AS
3 SQLINT 0 4 '~' 3 countryID SQL_Latin1_General_Cp437_Bin
4 SQLBIT 0 1 #@#@# 4 majorCurrencyFlag SQL_Latin1_General_Cp437_Bin

...and here's the first few rows of the text file:

1'~'United Arab Emirates Dirham'~'211'~'#@#@#2'~'Afghanistan
Afghani'~'1'~'#@#@#3'~'Albanian Lek'~'2'~'#@#@#4'~'Armenia
Dram'~'11'~'#@#@#

The last column in each row is NULL
===================================
Peter A. Schott <pschott@no.spamm.hear.drivefinancial.com> wrote in message news:<kgfuk0taj6ftuamr3iqco6n6iotarbm2c3@4ax.com>...
> Ran into something similar recently. If it's possible to open the file, I
> would grab a copy of "Notepad2" (or something similar) and take a look at it
> with the line endings turned on. My problem was that multiple rows ended in
> CR instead of CRLF. This program also allowed a really easy conversion by
> selecting the File-Line Endings and changing to one format (CR or LF), then
> back to CRLF.
>
> www.flos-freeware.ch
>
> -Pete
>
> Teretsky@gmail.com (Teretsky) wrote:
>
> > I'm trying to get a bulk insert job to run from within a DTS task.
> > When I run the DTS task "manually" by clicking Package/Execute from
> > within the designer or by right clicking and choosing [execute
> > package] from Enterprise manager, the package runs smoothly.
> >
> > When I try using a job to run the DTS package however I get one of the
> > two following errors:
> >
> > Unexpected end of file (EOF) encountered
> >
> > Column is too long in the data file for row 1, column 1
> >
> > Again, when run manually, this doesn't occur and my tables get loaded
> > with the data. Only when run through a job do I get these errors. From
> > the looking around I've done, these errors seem to be symptomatic when
> > using BULK INSERT or BCP from a command line, not within DTS. I've
> > also tried using format files but, the errors still occur.
> >
> > Any help would be greatly appreciated.