Import Files with Date-stamped Filenames or Paths

MAK [Muthusamy Anantha Kumar]

DTS packages are fantastic features available in SQL Server 7.0 and SQL server
2000. One outstanding feature in the DTS package is the ability to run ActiveX scripts.
ActiveX gives us an added advantage in handling many situations; we will
discuss how to handle three of these situations.

1. File-names with date-stamp as a prefix or suffix or root
   word.

   Eg: \\FileServer\SharedFiles\Bankthree-20030218.txt

2. File-paths with date-stamp as a prefix or suffix or root
   word.

   Eg: \\FileServer\SharedFiles\20030218\BankFour.txt

3. Handling only previous business day date-stamped files

Process Involved

Create Connection to Source files and database.

Give the source file names of SourceFile1 and SourceFile2
to any existing files, for example c:\autoexec.bat. We are going to control the
Path of the connections through ActiveX Controls.

Create Transformation between Sourcefiles and Database connections.

Now create two ActiveX scripts, one for each sourcefile.