Zip Files in SSIS With a Script Task and ZipFile

This is a guest post by Kevin Hazzard. To zip files in SSIS, skip the third-party tool. The .NET ZipFile class does the job inside a Script Task.

Kevin Hazzard is a database expert and an ETL developer. Kevin shares here a simple way to build Zip files inside an SSIS package.

A Problem Everything in SSIS Could Solve Except One Step

David, my child, is a database and ETL developer like me. David came to me with an interesting problem. David used SSIS to dump tables into text files. The files had to be zipped before they went out by FTP. Everything needed for the job was built into SSIS, except the zipping.

In the Unix world, I’d say to pack the folder into a tar ball from the command line. Windows was late to compressed folders. Some compression lives in the Windows shell, but zipping a folder from the command line, without installing software, isn’t straightforward. Doing it from inside SSIS is harder still. Archiving and unarchiving should be something Windows does with ease.

Why Not Use a Third-Party Tool?

Search the web for zip on the Windows command line, and you find many third-party tools. Narrow it to zip in SSIS, and you find the same tools. They sit inside libraries that must be installed in the Global Assembly Cache. Nobody likes third-party tools in SSIS, least of all for a problem that looks this simple. There are legal and security questions to answer. Deploying and maintaining installed software on every ETL server adds lingering work for someone.

I assumed those results showed the best-known way to zip files in SSIS. David came back after an hour with a better one. The .NET Framework 4.5 and newer include a class named ZipFile in the System.IO.Compression namespace. One of its methods, CreateFromDirectory, does the job.

Zip Files in SSIS With a Script Task

David dropped a Script Task onto the control flow. David set the script project’s .NET Framework version to 4.5 and added a few lines of C#. The two parameters, vZipFileName and vExtractDirectory, reach the script as SSIS variables. The code below is C# for the Script Task. It is not T-SQL.

string zipPath = Dts.Variables["User::vZipFileName"].Value.ToString();
string sourceDir = Dts.Variables["User::vExtractDirectory"].Value.ToString();
if (System.IO.File.Exists(zipPath))
    System.IO.File.Delete(zipPath);
System.IO.Compression.ZipFile.CreateFromDirectory(sourceDir, zipPath);
Dts.TaskResult = (int)ScriptResults.Success;

The script first checks whether the Zip file exists and deletes it if it does. David found by repeated testing that the delete is required, because CreateFromDirectory fails when the target already exists. Aside from the .NET Framework upgrade, there is nothing to install. No third-party tool and no library in the Global Assembly Cache is needed.

What Else the ZipFile Class Does

Other methods in the ZipFile class help when a package must read data from compressed files. ExtractToDirectory unpacks an archive, and Open lets you read single entries. It’s that easy. I was pleasantly surprised by David’s quick research, the out-of-the-box thinking and the elegant solution that came from it. I hope this way of handling Zip files helps your SSIS journeys as it helped David’s proud parent.

What to Remember

To zip files in SSIS, you need one class and one method call. Target .NET Framework 4.5 or newer, pass the paths in as variables, and delete the old Zip file first. The class is part of .NET, so there is nothing extra to deploy to each ETL server.

The answer was already part of .NET Framework 4.5, and it took an hour of research to find it. Search results didn’t point to it. A good habit is to check what the platform already offers before you reach for a third-party tool.

Note from Pinal: The code above was not run inside SSIS, because the test server has no SSIS runtime. A console program on .NET Framework 4.8 made the same calls. ZipFile needs a reference to System.IO.Compression.FileSystem, and a Zip file placed inside the folder being zipped throws an IOException. PowerShell’s Compress-Archive is a no-code option, but test its size limit before you use it for large archives.

A zip step is not a tool to buy, it is a class you already have.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.