Problem ith DTS and delimited file with empty last column

Hello,

I'm not getting any response to this on the SQLDTS newsgroup, so I
thought that I would try here:

I just ran into this problem and I can't find any other mention of it
through Google. I have a text file that is comma-delimited. It also
uses double quotes as text identifiers. A new column has been added to
the file, but currently has no values. I would like to finish my
development so that when it does finally get some values, they will be
imported as well. The problem is, the last column does not show up in
DTS.

I can reproduce this problem easily enough... create a text file with
the following two lines in it:

1,"test",
2,"test2",

Now, create a new DTS package and add a text file connection. Point it
to the new file and go through the properties for the file. You will
notice that on the second screen where it displays the preview of the
data there are only two columns shown.

This does not happen if there is no text qualifier or if at least one
row has the final column value filled. Is there any way around this
problem?

Thanks!
-Tom.

On Jan 18 2006, 11:44 am, "Thomas R. Hummel" <tom_hummel@hotmail.com> wrote
in news:1137602647.984522.221090@g14g2000cwa.googlegroups.com:

> I just ran into this problem and I can't find any other mention of it
> through Google. I have a text file that is comma-delimited. It also
> uses double quotes as text identifiers. A new column has been added to
> the file, but currently has no values. I would like to finish my
> development so that when it does finally get some values, they will be
> imported as well. The problem is, the last column does not show up in
> DTS.

As a kludgy workaround, you can try accessing the text file via ODBC rather
than DTS native text file provider. This will effectively use MS Jet Text
driver, which I believe does not have this problem. It will likely be
slower, and you will need to set up a DSN on both your workstation, for
development, and the server, for production runs of the DTS package.

--
remove a 9 to reply by email

Thanks for the suggestion. I ended up appending a header record to the
file with a simple copy command in DOS. I had to make sure that the
column names were all "Col001", "Col002", etc. (and capitalization DID
matter) so that my existing DTS package would still work without trying
to remap all of the columns. Hopefully by next month they will actually
be populating the column with data, otherwise I will build this header
append into my package.

Thanks again,
-Tom.

Dimitri Furman  wrote:
> As a kludgy workaround, you can try accessing the text file via ODBC rathe
r
> than DTS native text file provider. This will effectively use MS Jet Text
> driver, which I believe does not have this problem. It will likely be
> slower, and you will need to set up a DSN on both your workstation, for
> development, and the server, for production runs of the DTS package.