Using SSIS to zip files and email the zipped files

  • Hi Carolyn,

    Really cool. Well written.


  • Hi Carolyn & Charley,

    I am facing a similar problem. The task works well while executing directly from BIDS. But when i am trying to run it through a Sql Server agent job, it gets hanged out. I have checked the permissions on the folders for the account running the agent job and it has full permissions.

    Any insight will be helpful.



  • I usually run jobs using the SA account, and ensure that this service account has permissions on all the folders and is also an account that has permissions to send e-mails. You can add logging to your SSIS package to see whether you are getting any error mesages to help.

    Facts are stubborn things, but statistics are more pliable - Mark Twain

  • First of all, nice article.

    I worked on a similar project before, and I also used an Execute Process task with WinZip to handle the zipping part. However, I didn't use VB script to manipulate the variables for the Arguments in the Execute Process task or for the Send Mail task. Instead, I evaluated some Variables as Expression and built them from other variables and text. This method doesn't require any knowledge of VB scripting and just as flexible.


  • Did you save a copy to the server or are you running it from file? Do you have a proxy account set up to execute the package? Do you use any passwords in the package?


  • I am running it from the file itself. Is this a problem..?

    package does not use any credentials other then in the connections which I am supplying through the configurations file.

  • No Proxy account used.

  • If you run it from the file, you have to make sure that your SQLServerAgent account has permission to the path containing the package. Also this user account has to have pemission to the path in the zipping process. I recomend to use a proxy accont for the SQLServerAgent, that way you can easily set Windows permissions on any folder.


  • Thanks for your reply, sandor.

    owner account which is used to create\trigger the job has admin access on the box(windows and sql server both).

    If I disable this purticular execute task, rest of the package works well.

  • Add a switch to your execute command to run the zipping in the background.


  • justpiyushmittal-1127780, check out this link also. It has some useful tips.

  • clive-421796 (1/1/2010)

    How can I automate this so that I dont have to edit the Script Task every day and specify the date of previous days files to include, in this case 20091208 ( c:\temp\out\*_20091208.xls ).


    Set up a variable to build the day.

    Set up a variable to build the filename, based on the previous variable.

    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Below I listed my method using 7-zip which seaches the folder c:\temp\out\xand 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):

    Imports System.Data.OleDb

    Imports System.IO

    Imports Microsoft.SqlServer.Dts.Runtime

    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

    End Sub

    End Class

  • Hi Wayne, my post is just below yours (Post #841757).

    Can you have a look at it and reply with an example of variable for date.

  • Thanks Carolyn,

    what a brilliant Article.

    i however have modified it a bit to use WinRar and so far it works perfectly.

Viewing 15 posts - 46 through 60 (of 81 total)

You must be logged in to reply to this topic. Login to reply