SQL Server DTS Text File Imports 1

[eireanacht]

I am trying to create a DTS that will import a text file of "fixed width" The file I'm importing has multiple columns, and at the end of file, the last column, there is often, but not always, a lack of data. SQL Server does not seem to recognize the end of row when set to fixed width. It recognizes the end of row when data is present, but not when data is absent. I have imported the same file using MS Access and it works fine. I can't believe SQL Server DTS can not recognize the end of a row. Can anyone help or provide some suggestions I can try?

 

[mrdenny]

When you use a fixed with the end of row character must come after the last column marker should appear. If that column marker isn't there then it's not going to like it. With fixed with columns, that means that there must be data of some sort in that column.

What I mean by this is:

Col001    Col002   Col003      Col004
xxxkdssdf sadfklj  asdfsadf    sdfddddd
kfdkd     sdfkj    asdflkjadsf sdjdsj
fsdafsdf  dsfasdf  sdafsdaf
sdfjlksdj sdafsdaf dsfasdfasdf sadfsadf

It will Fail on Row 3 because the last character of row 3 is before it's expecting the row to end.

DTS isn't the most intelegent program when it comes to importing data. We are supose to be getting a much cooler version of DTS when SQL 2005 ships.

BCP or Bulk Insert might be able to handle this better.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[eireanacht]

Thanks very much for shining a light on this dingy process, thus far. I'll keep looking for options, but you've already given me more information in a few moments than I've been able to find otherwise. Thanks again, very much.

 

[mrdenny]

no problem. I'm not sure how picky BCP is, it might work out better for you. Bulk Insert won't work, because you can't (as far as I can remember) use fixed with columns, but I know that BCP can.

Denny

--Anything is possible. All it takes is a little research. (Me)