Import data from text files

• netedk

  SSC Veteran

  Points: 227

  More actions

  February 5, 2003 at 9:51 pm

  #105621

  I am new to DTS and am not sure if DTS is a better option that a normal sql script. Here's the task I need to do:

  I have to read raw data from a text file, apply business rules, and if all is OK insert/ update data.

  The text file lies on another server. After reading the text files I have to loop thro' each record to check if the record already exists in table. The check also depends on parameters passed. For example:

  say the field names are email, fax, first_name, last_email.

  If email is not blank, then just check if email exists in table. If email is blank, then check is fax+first_name+last_name exists in table (assumption is that emails are unique to users, but fax is not. and that fax+first_name+last_name is unique for users)

  If records exists, update it; else insert it.

  My question is:

  1. Can this be done using DTS?

  2. If yes, Is DTS better than writing a general script? What would be the advantages/ disadvantages of using these in terms of complexity and performance?

  3. Can a DTS package be called from an ASP page?

  Thanks in advance.

  DK

  Edited by - netedk on 02/05/2003 9:59:19 PM

• mhweiss

  SSCarpal Tunnel

  Points: 4284

  More actions

  February 6, 2003 at 9:27 am

  #448237

  DTS is fine for what you want to accomplish in my opinion. Import your text file into a staging table that includes a 'record_exists' colummn (tinyint). After your records are in the staging table, join that table to the final destination table on the fields that need to contain identical values in order for the record to 'exist'. Update record_exists (set = 1, 0 should be the default on the column) then update all recs in permanent table where record_exists = 1 and import all recs where record_exists = 0. Check out sqldts.com for an article on running DTS from ASP...I believe there is an article there describing how to do that.

  hth,

  Michael

  Michael Weiss

  
  Michael Weiss

• philcart

  SSC-Forever

  Points: 47796

  More actions

  February 6, 2003 at 2:50 pm

  #448293

  If the volume of data you're importing is large, consider using BULK INSERT, either the DTS task or the TSQL statement. Check out "Optimizing Bulk Copy Performance".

  In these cases, I've found that DTS can be a great little workflow management tool. Just plan out all the steps you have to do and setup the workflow in the package.

  Thanks

  Phill Carter

  --------------------
  Colt 45 - the original point and click interface