Below I listed my method using 7-zip which seaches the folder c:\temp\out\x\
and its subfolders for all files with *_20100104.xls , only includes it in the zip file,
20100104 is based on previous date.
Would like to automate the process of changing the date, but so far nobody seems to know how.
(I attached the 7-zip file, simply download, unrar/unzip with winrar, install).
(7-zip is freeware, you dont need to pay for it or for upgrades).
On page 7, I posted my winrar method, both are similar, but I prefer this 7-zip method.
Copy code below to Script Task of Visual Studio (BIDS):
Public Class ScriptMain
Public Sub Main()
Dim InFolder, InFileType, OutFileName, OutExecutable, OutSubject, OutFolder, OutMessage As String
Dim FileDate, strDay, strMonth, strYear As String
InFolder = Trim(CStr(Dts.Variables("User::InFolder").Value))
OutFolder = Trim(CStr(Dts.Variables("User::OutFolder").Value))
InFileType = Trim(CStr(Dts.Variables("User::InFileType").Value))
strDay = Right("0" + CStr(Day(Now)), 2)
strMonth = Right("0" + CStr(Month(Now)), 2)
strYear = CStr(Year(Now))
FileDate = strYear + strMonth + strDay
OutFileName = "Customername" + FileDate + ".zip"
OutExecutable = " a -tzip -ir!c:\temp\out\x\*_20100104.xls """ + OutFolder + OutFileName + """ """ + InFolder + "*." + InFileType + """"
OutSubject = "Attached Zipped Files:- " + OutFileName
OutMessage = "Zip Successful"
Dts.Variables("User::OutExecutable").Value = OutExecutable
Dts.Variables("User::OutFileName").Value = OutFolder + OutFileName
Dts.Variables("User::OutSubject").Value = OutSubject
Dts.Variables("User::OutMessage").Value = OutMessage
Dts.TaskResult = Dts.Results.Success