How to determine folder from one month ago.   

Hi,

I have a DTS package that is run once a month using a text file sent to us 
by our customer.  Each month we get a new data file and it's placed in a new 
folder with the current date.  The format of the date is YYMMDD.  So for 
instance the August file was placed in the folder created and named as 080806.

There is only one folder for each month and they are not created on the same 
day.  For example, the last three folders created are named as 080806, 
080707, and 080609.

The DTS package needs to have one of its text file sources changed each 
month so that it's pointed to the data file from the previous month.  In this 
case it should be pointed to the folder named as 080806.  I'm having 
difficulty in finding a way to do this.  I'm using an activeX script to do 
this and am looking for code that can accomplish this.

Any help would be appreciated.

date: Tue, 2 Sep 2008 14:49:01 -0700   author:   Jim Moberg

Re: How to determine folder from one month ago.   

On Sep 2, 11:49 pm, Jim Moberg 
wrote:
> Hi,
>
> I have a DTS package that is run once a month using a text file sent to us
> by our customer.  Each month we get a new data file and it's placed in a new
> folder with the current date.  The format of the date is YYMMDD.  So for
> instance the August file was placed in the folder created and named as 080806.
>
> There is only one folder for each month and they are not created on the same
> day.  For example, the last three folders created are named as 080806,
> 080707, and 080609.
>
> The DTS package needs to have one of its text file sources changed each
> month so that it's pointed to the data file from the previous month.  In this
> case it should be pointed to the folder named as 080806.  I'm having
> difficulty in finding a way to do this.  I'm using an activeX script to do
> this and am looking for code that can accomplish this.
>
> Any help would be appreciated.

Hi Jim,
the file source connection could dynamically be changed with the
Dynamic properties Task or even inside an Activex Script Task (that
obviously must be executed before any data pump task uses that
connection).
For each month (for each pkg execution) you must then pass the correct
directory name...

I dont' know if the following script could be embedded in an ActiveX.
I got it from a .vbs script that I use to retrieve and delete old
directories and files but "outside" a dts package. Opportunely
adapted, I believe this could fit your needs.
Alternatively you can also launch it within an execute process task,
in order to get the correct directory/file, copy it and set the name
to a default value (say, "CurrentMonth"); then set the file source
conn to point to this always-the-same value.

---
strOutputFile = "E:\FolderList.txt"
strComputer = "."
strDrive = "E:"
strDir = "\\cezanne\\"
strWQL = "Select * From Win32_Directory Where Drive = '" & strDrive &
"' And Path = '" & strDir & "'"

Const For_Appending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strOutputFile) Then
  	Set objTextStream = objFSO.OpenTextFile(strOutputFile,
For_Appending)
Else
  	Set objTextStream = objFSO.CreateTextFile(strOutputFile)
End If
objTextStream.WriteLine(" --- Folder List --- START ---")
objTextStream.WriteblankLines(1)

Set objWMIService = GetObject ("winmgmts:\\" & strComputer & "\root
\cimv2")
Set objPRNDir = objWMIService.ExecQuery (strWQL)
For Each objDir in objPRNDir
    		objTextStream.Write(objDir.Name) & vbtab & vbtab &
objTextStream.WriteLine(objDir.path)
Next
objTextStream.WriteLine(" --- Folder List --- END   ---")
objTextStream.Close

' Cleanup
Set objTextStream = Nothing
Set objFSO = Nothing
Set objWMIService = Nothing
Set objDir = Nothing
Set objPRNDir = Nothing
---

Keep us posted
M.

date: Thu, 4 Sep 2008 02:44:44 -0700 (PDT)   author:   matteus