Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using SSIS to zip files and email the zipped files

By Carolyn Richardson, (first published: 2008/09/22)

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.

Add Variables:-

Firstly in SSIS you need to create the variables to be used throughout.

Remember in SSIS variables are case sensitive.

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.

Next add a script task:-

You will need to set the ReadOnlyVaraibles to:

User::InFolder,User::OutFolder,User::InFileType

And the ReadWriteVariables to:-

User::OutExecutable,User::OutSubject,User::OutFileName,User::OutMessage

 

Then in the Design script add the following scipt:-

Imports System
Imports System.Data.OleDb
Imports System.IO

 

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
End If

If Len(strMonth) = 1 Then
strMonth = "0" + strMonth
End If

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

End Sub

End Class

-------------------------------------------------------------------------------------------------------------------------------------------

 

This script creates a zipped file called Archive with a date appended for good measure. Alter the subject (OutSubject) and Message (OutMessage) as required.

Next add a Execute Process task:-

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.

Add a SMTP connection

In Connection Managers add a smtp connection pointing to your mail server


Lastly add a send mail task:-

Using the Smtp connection that you just created. Add a From and To email address. Normally I set the From connection to be Servername at my companies domain.

Next add the global variables in the Expressions:-

You need to add the OutFileName as FileAttachments, the OutMessage as MessageSource and OutSubject as Subject, as shown in the diagram.

Link all the task together with On Success connections and you should be ready to go. Your final package should look like this:-

 Hopefully this article has given you a good place to start with zipping files and sending files using SSIS.

Total article views: 30874 | Views in the last 30 days: 112
 
Related Articles
FORUM

dts.variables in vb-script task

dts.variables in vb-script task

FORUM

How can I SSIS Concatenate DTS.Variables in Script Task or anywhere?

This is invalid: 'Dts.Variables("gsFileName").Value = Dts.Variables("gsFilePath").Value & Dts.Variab...

FORUM

SSIS Help

I have an SSIS package that I need to password protect (it can be static) can anyone help? I use 7 ...

FORUM

Using SSIS to zip files and email the zipped files

Comments posted to this topic are about the item [B]Using SSIS to zip files and email the zipped fil...

FORUM

Problem with DTS.variables in script task

Hi, I'm having a problem with the DTS.variables("MY_VAR").Value.ToString component in my script tas...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones