sql server dts: Hello,

I copied a DTS vb6 package to vb.net and can now import
data in my vb.net app from a text file (or MS Access mdb)
to Sql Server. What I would like to do is to have DTS
pull the data directly from a data table (ADO data table
in the app - memory table). Is this doable at this time?
Or does the data have to reside on the harddrive for DTS
to be able to read it? I heard that ADO version 2 will be
able to do bulk data transfer from a .Net app to Sql
Server like DTS. Can DTS do this? maybe there is some
commandline switch or something to read from memory? I
don't know that much about DTS right now and what the
limitations are. Any suggestions appreciated.

Thanks,

Well, my situation is that I retrieve bulk data on a daily
basis from a Non-Microsoft source. I have been connecting
to this source with its object library data connection and
then using ADO to read/write the data to Sql Server. We
are talking around 1 million records a day. My job is to
pull the data and write it to Sql Server. Everyday, the
big people want to see all of their records/data from the
data source into the Sql Server tables. How A gets to B
is immaterial to them. They don't care who can get A to
B. Right now it happens to be me. I was thinking it
would be faster to read the data into ADO.Net data tables
in my VB.Net app and then bulk copy that to Sql Server
like what DTS does with Text Files, MS Access data, etc.

Right now I am experimenting with writing the data from
the ADO.Net dataTables to Text files and then using DTS to
pick up that data. Writing the data to the .Net data
Tables is faster than writing each record to the Sql
Server Table directly row by row (200 fields per record -
the big people only care about getting from A to B - their
way and whoever can make that happen).

Row by Row writing is a lot of I/O. I can write the data
to a text file from the data tables much quicker than
writing directly to the Sql Server Tables and DTS seems to
work OK picking up the data from there (although I am
having some issues with some unsupported chars in some of
the record fields with DTS - issues that I don't get when
I write the data directly to the Sql Server tables using
ADO). When I compensate for the unsupported chars, this
may use up the time I saved by writing to the textfiles.
I will do some time tests and see if I get any performance
gain using DTS with the text files instead of writing row
by row with ADO.

Rich


[quoted text, click to view]