Looping through files for importing

Hello all,

I have 60 text files that I need to import into a DB. I want to figure out a way to loop through the files automatically. Can this
be done in the DTS Package?

I understand how to loop through all files in a folder using VB/VBA, so I can manage that if I save the package as a VB File. The
only problem if I go that route is that there is a field missing in each data file (the Month End Date). But each file consists of
one month and conveniently has the month end date in the file name, so I can populate the field by extracting the date from the file
name.

How I normally would do this in a DTS Package is:
1. Select the file using the Open File Dialog Task I downloaded from www.SQLDTS.COM (store the path\filename in a global variable)
2. use an ActiveX Script Task to extract the date in the file name global variable and store it in a date global variable.
3. then in my Transform Data Task, I would have an ActiveX Script transformation assigning that date global variable to the MEDate
field.

This works okay, but it is one file at a time with some interaction from me selecting each file.

Now I can remove the Open File Dialog Task, save the package as a VB file, setup the VB code to loop through each file, and
extract/store the date from the file name in a date variable in VB.

The thing I don't understand is how to pass the date stored in that variable to the date global variable in the Package/Transform
Data Task code in VB. I don't understand the VB code that SQL Server comes up with very well.

Any help that anyone can provide will be greatly appreciated,

Conan Kelly


.

Relevant Pages

  • Re: Looping through files for importing
    ... I understand how to loop through all files in a folder using VB/VBA, ... so I can manage that if I save the package as a VB File. ... populate the field by extracting the date from the file name. ... then in my Transform Data Task, I would have an ActiveX Script ...
    (microsoft.public.sqlserver.dts)
  • Re: Looping through files for importing
    ... I understand how to loop through all files in a folder using VB/VBA, ... so I can manage that if I save the package as a VB File. ... populate the field by extracting the date from the file name. ... then in my Transform Data Task, I would have an ActiveX Script ...
    (microsoft.public.sqlserver.dts)
  • Re: Mixed Simulation of Design (VHDL and Verilog)
    ... Dependency exists between package a and package BODY b ... SUBTYPE c_range IS natural RANGE a'RANGE; ... c_loop: ... Segmentation violation if a subtype is used as an index ...
    (comp.lang.vhdl)
  • Fun with Tasking
    ... Below is sample code I made representing something I wanted to do with ... this, as I mentioned, "exec" is representative of a family of several ... package Convert is new System.Address_To_Access_Conversions ... end loop; ...
    (comp.lang.ada)
  • Re: DTS Workflow Question
    ... >I am using SQL Server 2000 and I am writing a DTS package that loops ... >Script handling the loop routine. ... You could duplicate the ActiveX Script to restart the loop, ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
Loading