Re: vb.net dataTbl can DTS read?

From: Rich (anonymous_at_discussions.microsoft.com)
Date: 07/14/04

  • Next message: Chad Ehret: "Using a parameter in a subquery" 
    Date: Wed, 14 Jul 2004 13:13:05 -0700

    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

    >-----Original Message-----
    >In article <2c49d01c469d5$7f143dc0$a601280a@phx.gbl>,
    Rich wrote:
    >> 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,
    >> Rich
    >>
    >
    >Currently you cannot have a DataReader Source/ ADO rowset
    as the source
    >for a DataPump. Yukon may well be a different story
    >
    >Why does this have to be an ADO data table? Why can this
    not be
    >materialised into a View or a stored proc?
    >
    >
    >Allan Mitchell (Microsoft SQL Server MVP)
    >MCSE,MCDBA
    >www.SQLDTS.com
    >www.konesans.com - for all your consultancy needs
    >
    >
    >
    >.
    >

  • Next message: Chad Ehret: "Using a parameter in a subquery"

    Relevant Pages