Professional Web Applications Themes

Copy file to database - Microsoft SQL / MS SQL Server

Hi, I have a single comma seperated file that I need to copy to a single table in a database, this process will occur at least once every single day This is not a problem, In fact I have inplemented a DTS package to do it, and re run this everyday at a certain time. My problem is whether this is the correct way to do it. just for the record it works fine. However I keep reading in places that DTS should only be used for one-time transfers, which leaves me wondering why there is an option to save ...

  1. #1

    Default Copy file to database

    Hi,

    I have a single comma seperated file that I need to copy to a single table
    in a database, this process will occur at least once every single day
    This is not a problem, In fact I have inplemented a DTS package to do it,
    and re run this everyday at a certain time.

    My problem is whether this is the correct way to do it. just for the record
    it works fine.

    However I keep reading in places that DTS should only be used for one-time
    transfers, which leaves me wondering why there is an option to save the
    package if it should only be needed once??

    Some people say to use bcp, but then i have read in other places that a bulk
    insert statement runs faster, maybe I am wrong but I though bulk insert was
    like a wraper for bcp, and that executing a xp_cmdshell(bulk insert .....)
    amounted to the same thing.

    can anybody please point out the differences between these methods or point
    me to an article that compares/contrasts these.

    just for the record my comma seperated file i am importing is rougly 1000
    line long..


    cheers

    martin.


    Martin de vroom Guest

  2. #2

    Default Re: Copy file to database

    DTS is a fine way to do what you wish...If you are using a DTS
    transformation and If performance is satisfactory that will be fine... If
    you wish to make performance faster you may use the bulk insert task of DTS,
    this use a TSQL version of bcp to bulk insert records into a table very
    quickly...

    "Martin de vroom" <martindevroomhotmail.com> wrote in message
    news:OYvgAOvPDHA.452TK2MSFTNGP11.phx.gbl...
    > Hi,
    >
    > I have a single comma seperated file that I need to copy to a single table
    > in a database, this process will occur at least once every single day
    > This is not a problem, In fact I have inplemented a DTS package to do it,
    > and re run this everyday at a certain time.
    >
    > My problem is whether this is the correct way to do it. just for the
    record
    > it works fine.
    >
    > However I keep reading in places that DTS should only be used for one-time
    > transfers, which leaves me wondering why there is an option to save the
    > package if it should only be needed once??
    >
    > Some people say to use bcp, but then i have read in other places that a
    bulk
    > insert statement runs faster, maybe I am wrong but I though bulk insert
    was
    > like a wraper for bcp, and that executing a xp_cmdshell(bulk insert .....)
    > amounted to the same thing.
    >
    > can anybody please point out the differences between these methods or
    point
    > me to an article that compares/contrasts these.
    >
    > just for the record my comma seperated file i am importing is rougly 1000
    > line long..
    >
    >
    > cheers
    >
    > martin.
    >
    >

    Wayne Snyder Guest

  3. #3

    Default Re: Copy file to database


    Martin,
    DTS is great for running a regular job/series of jobs. Don't know where
    you got the idea from that it is not recommended. It has some neat
    features to change the process depending on success or failure (such as
    sending e-mail etc.) Fairly sophisticated packages can be developed -
    specially for repeated running !

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    malcolm k Guest

Similar Threads

  1. copy file
    By Rob in forum PHP Development
    Replies: 1
    Last Post: December 30th, 04:00 PM
  2. Copy database from MySQL to MSAccess
    By Kjell Hansen in forum PHP Development
    Replies: 3
    Last Post: September 2nd, 10:01 AM
  3. File copy
    By zafer in forum ASP
    Replies: 2
    Last Post: July 24th, 01:05 PM
  4. How to copy records from one database to another one ?
    By Jesus Gonzalez in forum FileMaker
    Replies: 3
    Last Post: June 28th, 03:03 PM
  5. Copy Database from NT to W2K
    By Giuseppe in forum Oracle Server
    Replies: 3
    Last Post: December 26th, 11:39 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139