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

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

Post by Sarah Henwo » Thu, 14 Jun 2001 04:46:21

In your DTS package create the tasks to import the data from the text file
into a table in the SQL Server database.  Then you can create additional
Execute SQL Tasks in the same DTS package to perform the transformation
needed.   Based on the information you provided in your first email an
example could be:

In the same DTS package:
1.  Perform a Bulk Insert Task to import the data from the text file into a
table that has the SQLKeyId and UpdateKey columns.
2.  Then perform an Execute SQL Task that performs the UPDATE against the
existing table and then drops the temp table.

If all you need to do is perform SQL update statements once the data has
been imported into a temporary table then it is not necessary to use
ActiveX for these type of operations - Execute SQL Tasks are appropriate
and can all be done in the same DTS package.

For more information on using the DTS Designer and setting up packages with
different tasks, please see SQL Server Books On-Line -> DTS Designer and
the topics DTS Package Management, Saving a DTS Package and DTS Package
Component Configuration.

Sarah Henwood
Microsoft SQL Server Support


1. Importing a Text File via DTS and adding the File Name as a column via DTS

Hello - I did a brief search on this subject and did not find anything in
this newsgroup.

Here's my problem:

I have a series of text files which all have the same format and structure.
They are files my company receives on a daily basis and uploads for use on
that day. The files do not feature a date field but the date they are for is
identifiable via the file name, e.g., EXT30_20010518.txt would be today's.

As is typical we now need to do historical analyses using these files but
since they do not feature a date column any uploaded files would be
meaningless in their current format. I need to add a date field and extract
the date from the file name. To your knowledge, can I do this via a DTS
transformation? (Even if I could simply get the file name in a column I
could do parsing in SQL).

Any tips, help or resources would be greatly appreciated. Thanks for your


2. ADO 2.6 bug... no error raised from MSDASQL provider if adExecuteNoRecords

3. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

4. EXPORT, DBA Studio VTK1000 error, connect fail for Oracle Management Server

5. Help with DTS and ActiveX Script to import text file to a table

6. SQL 7.0 in Windows NT 4.0 cluster

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

8. Building a text file using Stored Proc/DTS

9. Call a VBScript file from a stored proc.

10. calling stored proc from stored proc via variable

11. Using DTS to use one field to update another (via ActiveX)

12. Open Text files into a recordset in VBScript in a DTS Package