Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: DTS for 100 text files

    Hello DBA's:

    I want to upload 100 text files to a single table on SQL Server 2000.
    Records from these text files would be selected on the basis of a where clause.

    What would be the best way of accomplishing this? Using DTS, I can do only one file at a time. Is there a faster approach.

    Thanks

    Vivek

  2. #2
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    Till the time you get a better solution you can use some file concatenation utility to join all those files into a single one and do the data load using DTS.

    http://www.jddesign.f2s.com/concat-s.htm

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    Does this utility join files OR append them one after the other. I want to append the records not join them? Let me know.

  4. #4
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    it appends them

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why don't you write a sproc that interogates a directory, finds all the file names, then bcp them in, one by one [shudder] with a cursor[/shudder] and then archive them after it's loaded...

    What format are the files in? Are they the same structure?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Posts
    12
    sounds like a job for perl (every admins friend) or heaven forbid vbscript.

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    You can use dts - using an activex script task using the fso object to find the files you need to load. Or windows shell scripting using bcp (bulk copy). Is this a one time event or is there a potential for more ?
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by Brett Kaiser
    Why don't you write a sproc that interogates a directory, finds all the file names, then bcp them in, one by one [shudder] with a cursor[/shudder] and then archive them after it's loaded...

    What format are the files in? Are they the same structure?
    Brett ... no way you can accomplish this without a cursor ....

    I challenge everyone to make a set based solution for the same

    LOL ... am working on a similar procedure ... need to uload files daily of the form LocationCD_Extracttype_yyyymmdd.txt .... tab seperated files .. any ideas other than bcp and dts ..
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Sep 2003
    Posts
    176
    It is a one-time event. I have no experience in shell scripting, unfortunately.
    Originally posted by rnealejr
    You can use dts - using an activex script task using the fso object to find the files you need to load. Or windows shell scripting using bcp (bulk copy). Is this a one time event or is there a potential for more ?

  10. #10
    Join Date
    Aug 2003
    Posts
    39
    How about this?

    Create 2 DTS packages. An Outer and an Inner Package.

    The Outer package has an ActiveX script task the uses the FSO to indentify all the files in a directory, then for each file set the text file connection of the inner package to the path of the text file identified in the Outer package. Then call the Inner package which just has a text file connection and a DB connection with a trasnformdata task.
    Loop through all the files repeating the above.

    This would be very automated and fairly quick depending on the size of the text files. And actually pretty easy to implement.

    Steve

Posting Permissions

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