Multi-text file import

[dswitzer]

I need some conceptual help on how to do the following:

Evaery quarter, I have 6 text files that I import into SQL SERVER(2000). The file locations will be based on the quarter and the file names will remain static. For example:

Qtr 1 2004:
path: X:\2004_q1\file1.csv
path: X:\2004_q1\file2.csv

Qtr 2 2004:
path: X:\2004_q2\file1.csv
path: X:\2004_q2\file2.csv

So, I can set up a DTS pkg to link to the csv files for qtr 1 2004, but am struggling how to change that link to hit the new directory in qtr 2 2004. For this first step, I would like the DTS to prompt me for the directory - so I input "2004_q2" and then use that input to read the files. (I can work on automating that part when I get this running).

I assume an ActiveX component can trigger an inputbox and read that input (although I've never done this) -- if so, how can I change the paths of the text files?

Thanks for your input.

 

[dswitzer]

OK - s0 I researched and found a few new keywords on this issue "Dynamic Properties Task" being one of them and went down this route:

1. built activex script that prompts for a directory and builds globalvariables for the pathname of each file:

Function Main()
  DTSGlobalVariables("path").Value = InputBox("Type NCUA directory (yyyy_q#)", "NCUA Directory" , "")

  DTSGlobalVariables("FOICU_path").Value="\\pfsapp\galt\ncua\" &   DTSGlobalVariables("path") & "\foicu.txt"
  DTSGlobalVariables("FS220_path").Value="\\pfsapp\galt\ncua\" &   DTSGlobalVariables("path") & "\fs220.txt"
  DTSGlobalVariables("FS220A_path").Value="\\pfsapp\galt\ncua\" &   DTSGlobalVariables("path") & "\fs220a.txt"
  DTSGlobalVariables("FS220B_path").Value="\\pfsapp\galt\ncua\" &   DTSGlobalVariables("path") & "\fs220b.txt"
  DTSGlobalVariables("FS220C_path").Value="\\pfsapp\galt\ncua\" &   DTSGlobalVariables("path") & "\fs220c.txt"
  DTSGlobalVariables("FS220D_path").Value="\\pfsapp\galt\ncua\" &   DTSGlobalVariables("path") & "\fs220b.txt"
  Main = DTSTaskExecResult_Success
End Function
[\code]

I then setup another activex script msgboxing the variables to ensure they were truly global. I then double-checked that the variables were set to the proper path (and I can locate the files through a browser using this path).

Then, I setup a Dynamic Properties Task and set each of my 6 files "Data Source" to the corresponding global variable.

When I run the DTS, it seems that the global variables work, and the Data Source Property in the Dynamic Properties Task gets changed to the global variable.  But the data pump still brings in the data from the default file (like the Dynamic Properties Task is being ignored).

Any ideas on what to look at?

Thanks.