Import Files with Date-stamped Filenames or Paths

March 10, 2003

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.