Importing text files - DTS help needed

[JCC]

Hi all

Could someone help me with the following problem? Hours of googling
yesterday couldn't get me the answer. I'm using SQL 2000 and DTS and
trying to import a huge fixed width text file.

File is >1m rows and >200 columns and is defined by a proprietory (i.e. not
bcp produced) format specification of the form

Name Start Length
Fld1 0 20
Fld2 19 5
Fld3 24 53

and so on.

Tbe only way I've found to define the columns so that DTS can import the
file properly is to go through the wizard and click on the starts of each
column. I don't want to use bcp if possible (I did enough of that on
SQL7) - but surely there's a way to get DTS to read from a format file so I
don't have to click 200 times (with all the ensuing errors I could make).

Any help greatly appreciated.

Cheers
Rob

[Dan Guzman]

but surely there's a way to get DTS to read from a format file so I don't

have to click 200 times (with all the ensuing errors I could make).

Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.
--
Hope this helps.

Dan Guzman
SQL Server MVP

"JCC" <go******@wrong.address.comwrote in message
news:fd*******************@newsfe6-win.ntli.net...

Hi all

Could someone help me with the following problem? Hours of googling
yesterday couldn't get me the answer. I'm using SQL 2000 and DTS and
trying to import a huge fixed width text file.

File is >1m rows and >200 columns and is defined by a proprietory (i.e.
not bcp produced) format specification of the form

Name Start Length
Fld1 0 20
Fld2 19 5
Fld3 24 53

and so on.

Tbe only way I've found to define the columns so that DTS can import the
file properly is to go through the wizard and click on the starts of each
column. I don't want to use bcp if possible (I did enough of that on
SQL7) - but surely there's a way to get DTS to read from a format file so
I don't have to click 200 times (with all the ensuing errors I could
make).

Any help greatly appreciated.

Cheers
Rob

[JCC]

"Dan Guzman" <gu******@nospam-online.sbcglobal.netwrote in message
news:SY***************@newssvr14.news.prodigy.com. ..

>but surely there's a way to get DTS to read from a format file so I don't
have to click 200 times (with all the ensuing errors I could make).

Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.
--
Hope this helps.

Dan Guzman
SQL Server MVP

I have looked into this - but as I said, I don't want to mess around with
bcp if I can avoid it - I had no end of problems with bcp a while back.

It looks like I can't avoid it though!

Cheers
Rob