This article makes use of global variables throughout and shows how they can be used in SSIS, you can adapt these to make a package that can be made as flexible as you want. The package described here zips all files in a folder to a single archive zip file. To zip files this package uses 7-zip available from http://www.7-zip.org, it has some limitations on file sizes, but its Open source and freely available for download. You will need to install this first for the package to work. This is a useful application and some of the options available using this tool include the ability to password protect your zip files.
Firstly in SSIS you need to create the variables to be used throughout.
InFileType should contain the extension of the file type, in the example I am zipping txt files, it could be any other file type compatible for zipping or * for files of any type.
InFolder is the Folder where the files you want to zip up reside
OutExecutable is to be populated in the script. task so leave blank.
OutFileName is to be populated in the script. task so leave blank.
OutFolder is where you want to place the final zip file, it can be the same as the InFolder or any other specified location.
OutMessage is to be populated in the script. task so leave blank.
OutSubject is to be populated in the script. task so leave blank
a) You will need to set the ReadOnlyVaraibles to:
b) And the ReadWriteVariables to:
Then in the Design script. add the following script.
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 = CStr(Day(Now))
strMonth = CStr(Month(Now))
strYear = CStr(Year(Now))
If Len(strDay) = 1 Then
strDay = "0" + strDay
If Len(strMonth) = 1 Then
strMonth = "0" + strMonth
FileDate = strYear + strMonth + strDay
OutFileName = "Archive" + FileDate + ".zip"
OutExecutable = " a -tzip """ + OutFolder + OutFileName + """ """ + InFolder + "*." + InFileType + """"
OutSubject = "Attached Zipped Files:- " + OutFileName
OutMessage = "Add your message here"
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
This script. creates a zipped file called Archive with a date appended for good measure. Alter the subject (OutSubject) and Message (OutMessage) as required
The Executable should point to the location of 7-Zip, and its Working directory as in the diagram.
In the Expressions add the OutExecutable as the arguments as in the diagram.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14321372/viewspace-591110/，如需转载，请注明出处，否则将追究法律责任。