Welcome to another blog post on SQL Server concepts! In this article, we will explore how to use SSIS (SQL Server Integration Services) to zip files and send them via email. This can be a useful feature when you need to automate the process of compressing files and sending them to recipients.

Getting Started

Before we dive into the details, let’s make sure we have all the necessary tools and components in place. Firstly, you will need to install 7-zip, an open-source file archiver, which can be downloaded from the official website (http://www.7-zip.org). This tool will be used to zip the files in our SSIS package.

Next, we need to set up some variables in SSIS. Variables in SSIS are case-sensitive, so make sure to use the correct casing. We will need the following variables:

• InFileType: This variable should contain the extension of the file type you want to zip. For example, if you want to zip text files, you can set it to “.txt”.
• InFolder: This variable represents the folder where the files you want to zip up reside.
• OutExecutable: This variable will be populated in the script task, so leave it blank for now.
• OutFileName: This variable will also be populated in the script task, so leave it blank.
• OutFolder: This variable represents the location where you want to place the final zip file. It can be the same as the InFolder or any other specified location.
• OutMessage: This variable will be populated in the script task, so leave it blank.
• OutSubject: This variable will be populated in the script task, so leave it blank.

Script Task

Now that we have our variables set up, let’s add a script task to our SSIS package. In the script task, we need to set the ReadOnlyVariables to User::InFolder, User::OutFolder, and User::InFileType. And the ReadWriteVariables should be set to User::OutExecutable, User::OutSubject, User::OutFileName, and User::OutMessage.

In the Design script section of the script task, we will add the following script:

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 the current date appended to it. You can modify the subject (OutSubject) and message (OutMessage) as per your requirements.

Execute Process Task

Next, let’s add an Execute Process task to our SSIS package. The Executable property of this task should point to the location where 7-zip is installed. Set the Working directory accordingly.

In the Expressions section of the Execute Process task, add the OutExecutable variable as the argument.

Send Mail Task

Now, we need to add a Send Mail task to our SSIS package. Before that, make sure you have a SMTP connection set up in the Connection Managers section, pointing to your mail server.

In the Send Mail task, specify the From and To email addresses. You can set the From address to be the server name at your company’s domain.

Lastly, we need to add some global variables in the Expressions section:

• FileAttachments: Set this variable to OutFileName.
• MessageSource: Set this variable to OutMessage.
• Subject: Set this variable to OutSubject.

Link all the tasks together with On Success connections, and your SSIS package is ready to go!

Conclusion

In this article, we explored how to use SSIS to zip files and send them via email. By leveraging the power of global variables and script tasks, we were able to automate the process of compressing files and sending them to recipients. This can be a valuable feature in scenarios where you need to regularly send files to multiple recipients.

Hopefully, this article has provided you with a good starting point for implementing similar functionality in your own SSIS packages. Feel free to experiment and adapt the code to suit your specific requirements.