September 17, 2010 at 10:03 am
I'm having problems traversing subfolders. I have a main folder with nothing in it and then several subfolders with up to 2 files each. There are two VB script tasks inside a foreach file loop. The first script counts the number of files in the folder. The next script task deletes any files older than 1 day. There's a success expression and constraint between the two tasks where the second one will only execute if there are more than two files in the folder. The following variables have been declare in the package, and the scripts are below. The enumerator is set to the main folder. When I point the foreach file enumerator at one folder only, which has files in it, the package works and acts exactly as expected. However, I when I try to traverse the subfolders, it seems to only count the files and not delete the old ones. Any help with this would be greatly appreciated, as I've been messing with it for 3 days. Thanks.
User::edw_server_path STRING - set to the main folder
User::file_count INT32
User::days_keep INT32
Count Files Script Task
Read Only Variables: User::edw_server_path,User::file_count
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
'
Dim sDirectoryPath
sDirectoryPath = CStr(Dts.Variables("User::edw_server_path").Value)
Dts.Variables("User::file_count").Value = Dts.Variables("User::file_count").Value + 1
'MsgBox(Dts.Variables("User::file_count").Value.ToString())
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
DELETE Files Script
Read Only Variables: User::days_keep,User::edw_server_path
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
'
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld
'Archive Files
iDaysOld = CInt(Dts.Variables("User::days_keep").Value)
oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = CStr(Dts.Variables("User::edw_server_path").Value)
oFolder = oFSO.GetFolder(sDirectoryPath)
oFileCollection = oFolder.Files
'Walk through each file in this folder collection.
'If it is older than days_keep days, then delete it.
For Each oFile In oFileCollection
If oFile.DateLastModified < (DateTime.Now.AddDays(-iDaysOld)) Then
oFile.Delete(True)
End If
Next
'Clean up
oFSO = Nothing
oFolder = Nothing
oFileCollection = Nothing
oFile = Nothing
Dts.TaskResult = Dts.Results.Success
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
September 17, 2010 at 10:39 pm
dave.molyneaux (9/17/2010)
I'm having problems traversing subfolders. I have a main folder with nothing in it and then several subfolders with up to 2 files each. There are two VB script tasks inside a foreach file loop. The first script counts the number of files in the folder. The next script task deletes any files older than 1 day. There's a success expression and constraint between the two tasks where the second one will only execute if there are more than two files in the folder. The following variables have been declare in the package, and the scripts are below. The enumerator is set to the main folder. When I point the foreach file enumerator at one folder only, which has files in it, the package works and acts exactly as expected. However, I when I try to traverse the subfolders, it seems to only count the files and not delete the old ones. Any help with this would be greatly appreciated, as I've been messing with it for 3 days. Thanks.
I would suggest a change in package design if possible:
Step 1: Usig the Script task validate the folder content as you have mentioned earlier
Step 2: Create a new FST in delete directory content mode
Note: you might have to introduce new variables as source folder name is dynamic
HTH
Raunak J
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply