Hello,

I am having trouble importing data from a text file where
a line of text contains double quotes like this:

....
address|123 First street|"Sometown|IO|97165| ...

This one line only has one double quote. I am running dts
from a vb.net app. If I remove the double quote in the
data pull, DTS pulls the data in just fine. But, ideally,
I want to pull the data in just the way it comes from the
data source.

I looked at this line of code from the DTS package

oConnection.ConnectionProperties.Item("Text
Qualifier").Value = """"

Can I modify this to accept double quotes in my text
file? How do I modify it?

Thanks,
Ben

In message <78de01c4847c$02ffb4a0$a501280a (AT) phx (DOT) gbl>, Ben
<anonymous (AT) discussions (DOT) microsoft.com> writes
Can you not just set the Text Qualifier to nothing on the text file
connection? You can do this in the connection UI for example.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

I haven't tried this yet, but are you suggesting

oConnection.ConnectionProperties.Item("Text
Qualifier").Value = Nothing

This doesn't seem right. How do I set the Text Qualifier
to nothing?

oConnection.ConnectionProperties.Item("Text
Qualifier").Value = ""

perhaps? Or

oConnection.ConnectionProperties.Item("Text Qualifier") =
Nothing

And lastly, the syntax for setting something to Nothing in
VB.Net is kind of funny. How would that go?

Thanks,
Ben

Try-
oConnection.ConnectionProperties.Item("Text Qualifier").Value = ""

I recommend you get the package working in the designer first and then turn
it into code afterwards. Using the GUI to set unknown properties will be a
lot easier until you become more familiar with the inner workings of DTS.


--
Darren Green
http://www.sqldts.com

"Ben" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Thanks. I will try this. My dts package already works in
the vb.net app. But I have been having to strip off
double quotes when reading data from data source to text
file.

I was originally reading data from the data source and
writing it straight to sql server using ADO. But it seems
to be much faster to read the data to a dataset, close the
connection to the data source, write the data from the
dataset to a text file (takes only a few seconds for
100,000 records) and then use my dts package to push the
data to Sql Server. I am currently waiting for ADO.Net2
to come out - understand this version will have
functionality similar to DTS where I could push the data
directly from the dataset straight to sql server. Right
now, dataAdapter.Fill is out of the question. I am
dealing with hundreds of thousands of records per push.
Fill took forever (like over an hour or more). DTS
package took like 30 seconds, maybe less.

My thing is that I need to retrieve the source data in the
exact format that it is in. If there are double quotes,
asterisks, single quotes, carats, etc, Need to get em all.

Ben

In message <82c201c48536$a1d36350$a501280a (AT) phx (DOT) gbl>, Ben
<anonymous (AT) discussions (DOT) microsoft.com> writes
DTS for SQL Server 2005 will probably be a better bet compared to any
ADO improvements in the framework.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org