Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    25

    Question Unanswered: What to use instead of BCP?

    I want to use transaction logging and as we have a ton of BCP scripts need to change them.

    We move a LOT of data around and I hear that DTS is slow in comparison. Is there another way I can propose to move our data around other than DTS?

    Could we use replication perhaps? I'm not sure it would work (never used it) as I don't think you can select individual tables etc etc

    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Replication can be set up on individual tables.

    One thing you could do is set up a db just for receiving BCP data. once the data was loaded you could then move the data to it's perminant home. The second step would be logged.

    IMHO, get a couple of cheap PCs and play with replication. Microsoft's solution isn't the best I have seen but is very handy.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245

    Re: What to use instead of BCP?

    Have you actually tried DTS and found it lacking, or are you going off of what someone has said? I make extensive use of DTS and it works pretty well. I have moved tables with 1,000,000 + rows in less than 10 minutes (100MB network connection, 100 + columns).

    Of course mileage may vary based on hundreds of variables.

    In general, for best performance:
    1. Avoid ActiveX scripts in your transform packages (try to complete all the transformations in the SQL that generates the original recordset).
    2. For this reason, avoid the Data Driven Transformation Object and use only the "Copy Column" transformation task in your data pump.
    3. Consider using an "intermediary"; a separate server to run the DTS package(s) -- this server is neither the source nor the target, but an altogether different server.

    Try it out on a few tables and see the performance for yourself. I am not advocating that DTS is the only solution; just try it before discarding it based on something you have seen or read.

    Regards,

    Hugh Scott


    Originally posted by FunkyD
    I want to use transaction logging and as we have a ton of BCP scripts need to change them.

    We move a LOT of data around and I hear that DTS is slow in comparison. Is there another way I can propose to move our data around other than DTS?

    Could we use replication perhaps? I'm not sure it would work (never used it) as I don't think you can select individual tables etc etc

    Thanks

  4. #4
    Join Date
    Feb 2003
    Posts
    109

    dts is super fast

    dts is super fast--

    its a question of how your DB is designed.

    is it indexed correctly, do you drop indexes and then rebuild if you need to, etc. do you have the datatypes the smallest possible?

    etc

    you can always create a linked server, and then use the OPENQUERY function, or the OPENROWSET command in order to create a DSNLESS connection (i think)
    Access 2002 ADP Rocks my World

    Long live SQL Server and 64bit Windows!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •