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.

On Sep 2, 11:49*pm, Jim Moberg <JimMob... (AT) discussions (DOT) microsoft.com>
wrote:
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.

On Sep 2, 11:49*pm, Jim Moberg <JimMob... (AT) discussions (DOT) microsoft.com>
wrote:
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.

On Sep 2, 11:49*pm, Jim Moberg <JimMob... (AT) discussions (DOT) microsoft.com>
wrote:
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.

On Sep 2, 11:49*pm, Jim Moberg <JimMob... (AT) discussions (DOT) microsoft.com>
wrote:
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.

On Sep 2, 11:49*pm, Jim Moberg <JimMob... (AT) discussions (DOT) microsoft.com>
wrote:
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.

On Sep 2, 11:49*pm, Jim Moberg <JimMob... (AT) discussions (DOT) microsoft.com>
wrote:
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.

On Sep 2, 11:49*pm, Jim Moberg <JimMob... (AT) discussions (DOT) microsoft.com>
wrote:
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.

On Sep 2, 11:49*pm, Jim Moberg <JimMob... (AT) discussions (DOT) microsoft.com>
wrote:
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.