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.googlegr oups.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 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.

problem inserting data in table - all rows are not returned

The procedure is inserting data into this table :
create table temp_XML
(xml_data varchar(8000))
I ran the trace and proc is running fine. But all the data is not
returned when I do select * from temp_xml.
I did some research and limited data by using a where clause.
The proc ran fine in some cases and returns all data and does not runs
fine in some where clauses. What do I need to do to return all the
rows.
But the proc runs fine in production without any limiting where clause
I have different procs like this and some run fine in development and
not in production and vice versa. The procs have successfully ran in
the development before also.
any sugesstions on how to fix this.VJ wrote:
> The procedure is inserting data into this table :
> create table temp_XML
> (xml_data varchar(8000))
> I ran the trace and proc is running fine. But all the data is not
> returned when I do select * from temp_xml.
> I did some research and limited data by using a where clause.
> The proc ran fine in some cases and returns all data and does not runs
> fine in some where clauses. What do I need to do to return all the
> rows.
> But the proc runs fine in production without any limiting where clause
> I have different procs like this and some run fine in development and
> not in production and vice versa. The procs have successfully ran in
> the development before also.
> any sugesstions on how to fix this.
Check if there is a faulty trigger on the table, especially one that
can't handle multiple row inserts. Also, are you sure you are capturing
any error message that the proc might be generating?
If you need more help, please post your code if you can.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||There are no triggers in the proc.
The proc is running fine in the other database. Something is limiting
the rows inserted in the development database now, but it was fine a
few minutes ago.
Please let me know your email address and I will send you the proc.
David Portas wrote:
> VJ wrote:
> Check if there is a faulty trigger on the table, especially one that
> can't handle multiple row inserts. Also, are you sure you are capturing
> any error message that the proc might be generating?
> If you need more help, please post your code if you can.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --