Loading text files, DTS/Stored proc/TSQL/VB??

Loading text files, DTS/Stored proc/TSQL/VB??

Post by stev » Sat, 17 Jun 2000 04:00:00


I'm not too experienced with programming, but I need to import around 25
text files (10Kb - 70Mb) into SQL Server 7.0.

I know of serveral ways to accomplish this, which are through a DTS
package, Stored procedure (bulk insert) or from inside of VB and using
ADO. I need to make this easy to support and upgrade/scale.

I am asking for input regarding which is the fastest, most robust, and
most easily maintained method. Maintenance is especially important as
there may be new files to load, and modifications to existing ones.



Loading text files, DTS/Stored proc/TSQL/VB??

Post by Neil Pik » Sun, 18 Jun 2000 04:00:00

Steve - it really depends on the layout of the files and any processing you
need to do.  Either BULK INSERT or DTS will do the job for nearly all cases.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (428 entries) see
 forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
- SQL Public)
 or www.ntfaq.com/sql.html
 or www.sql-server.co.uk
 or www.mssqlserver.com/faq


1. Building a text file using Stored Proc/DTS

I need to create an XML file that represents our company tree.  The data and
relations are all contained in a single 'employee' database.  Because it is
impossible to predict the number of branches in the tree I can't use DTS to
drop a dataset into a file and using an xml file with the "FOR XML AUTO"
won't work either for the same reson.  These are the only 2 techinques I am
familiar with to create a text file from SQL.  I am a VB programmer and if
there was a way to construct an external text file using a stored procedure
(just like stringbuilder in VB) that might work best.  I already have a
stored proc that can perform the work, I just need a command(s) that allows
the code to write it out to a text file and give the file a name.  Does
anyone have any advice on where to look to find information on this, or how
to do this?


2. cannot register server with named pipe and mulitprotocol

3. Table Update Via Text File (DTS, Stored Proc, ActiveX, VBScript)

4. Cross-Tab query

5. Calling stored proc from DTS VB script function

6. Date Format Problems

7. dts error while loading empty fields from a text file

8. Windows Authentication

9. Loading Text File using DTS

10. Use DTS to load text file

11. Loading a Text File Through DTS via ASP Page

12. Problems with load text file in DTS

13. Loading Text Files with DTS