Zip and Unzip files using 7-Zip in SQL Server Integration Services


By:   |   Updated: 2019-03-19   |   Comments (5)   |   Related: More > Integration Services Development


A Powerful and Secure Alternative to SSIS

Free MSSQLTips Webinar: A Powerful and Secure Alternative to SSIS

SSIS is the de facto solution for SQL Server data integration and transformation. However, standard ETL processes are typically slow and result in data that is not always current which impedes decision making and business processes. Organizations are increasingly in need of better performing real-time data access without sacrificing critical access controls and data activity monitoring.


Problem

There is often a need to zip and unzip files that we get that need to process with SQL Server Integration Services (SSIS).  There are several different tools that exist that have this functionality.  In this tip, I will show the steps on how to zip and unzip files using 7-Zip as part of a SQL Server Integration Services package.

Solution

We can use any file extractor in SQL Server Integration Services, but for this demonstration I am going to use 7-Zip to zip and unzip folders and files. Before jumping into the SSIS package we should cover some basics of 7-Zip.

About 7-Zip File Archiver

7-Zip is a file archiver with a high compression ratio using LZMA and LZMA2 compression. Its free software and open source. It has self-extracting capabilities for 7Z format and strong AES-256 encryption in 7z and ZIP formats. To install 7-Zip, you can download 7-Zip for Windows.

After installing the 7-Zip on my PC, here is the 7-Zip installation location.

7-Zip installation location

This is the location where I want to zip files.

7-Zip BulkFiles location

Zip Files Using 7-Zip in SQL Server Integration Services Package

 I created a new SSIS project and named it "Zip_UnZip_Using7zip_SSIS" then dragged an Execute Process Task from the SSIS Toolbox to the Control Flow and renamed it "ZIP Data".

Execute Process Task in SQL Server Integration Services

As per MSDN, the Execute Process Task runs an application or batch file as part of a SQL Server Integration Services package workflow. Although you can use the Execute Process task to open any standard application, you typically use it to run business applications or batch files that work against a data source.

Here are the settings for the Execute Process Tasks.

RequireFullFileName The task should fail if the executable is not found at the specified location.
Executable Name of the executable to run.
Arguments Command prompt arguments.
WorkingDirectory Path of folder that contains executable, or click the browse button (...)and locate the folder.
StandardInputVariable Select a variable to provide the input to the process, or click New variable... to create a new variable.
StandardOutputVariable Select a variable to capture the output of the process, or click New variable... to create a new variable.
StandardErrorVariable Select a variable to capture the error output of the processor, or click New variable... to create a new variable.
FailTaskIfReturnCodeIsNotSuccessValue Indicates whether task fails if process exit code is different from the value specified in SuccessValue.
SuccessValue Specify the value returned by the executable to indicate success. By default, this value is set to 0.
TimeOut Specify the number of seconds that the process can run. A value of 0 indicates that no time-out value is used, and the process runs until it is completed or until an error occurs.
TerminateProcessAfterTimeOut Indicates whether process is forced to end after the time-out period specified by the TimeOut option. This option is available only if TimeOut is not 0.
WindowStyle Specify the window style in which to run the process.

Now right click the task and choose "Edit…" to open the Execute Process Task Editor and then click "Process" on the left and change the executable path, arguments and working directory as follows and keep the others as the defaults. I explain this further down.

SSIS Execute Process Task Editor

Executable

Put the full path to the executable as: C:\Program Files\7-Zip\7z.exe

Arguments

Provide the command prompt arguments: a -t7z "D:\BulkFiles.ZIP" "D:\BulkFiles\*.*"

  • I am going to archive the files using the "a" command argument.
  • The format is specified with the -t switch -t7z
  • Put all files into the zip file "D:\BulkFiles.ZIP"
  • From location "D:\BulkFiles\*. *"

Here is a link to the Command Lind Commands and here is a link to Command Line Switches.

Working Directory

The Execute Process Task will change directories to this location. If an EXE writes to an output file in the current directory, this file will be located wherever the working directory is pointing. Here I am going to add a zip file on D:\drive.

Running the SSIS Package to Zip Files

Now the package is set for archiving the data.

Running the SSIS Package with 7-Zip

As per the configuration, all files are zipped into BulkFiles on the drive "D:\".

BulkFiles from the SSIS Package with 7-Zip

Unzip Files Using 7-Zip in SQL Server Integration Services Package

I will add to the SSIS package another Execute Process Task to extract the files we just zipped up.

Unzip files in SSIS with 7-Zip

Here are the settings to unzip the files.

SSIS Execute Process Task Editor

Executable

Put the full path to the executable as: C:\Program Files\7-Zip\7z.exe

Arguments

Provide the command prompt arguments: e "D:\BulkFiles"

  • I am going to extract the files using the "e" command argument.
  • This will extract the file "D:\BulkFiles"

Here is a link to the Command Lind Commands and here is a link to Command Line Switches.

Working Directory

The Execute Process Task will change directories to this location. If an EXE writes to an output file in the current directory, this file will be located wherever the working directory is pointing. Here I am going to add a zip file on D:\drive.

Running the SSIS Package to UnZip Files

Here is the package.

Unzip the files in SSIS with 7-Zip

If we look on the D:\ drive we can see the extracted files.

Extracted Files from 7-Zip

SQL Server Integration Services Execute Process Task Use

  • The Execute Process Task can be used to execute an application, executable files, batch files inside a SSIS execution.
  • As per this scenario, we can also use RAR/Winzip/PeaZip/Jzip to perform compression/decompression of files and folders.
  • We can also execute standard applications like (Winword, Excel, Powerpoint, etc.) and custom application (C#.Net,Vb.Net,etc).
  • It’s easy to integrate variables, parameters and arguments.
  • If you need to execute Windows command line commands such as RENAME, DEL, COPY, DIR you use the cmd.exe utility with this task.
Next Steps


Last Updated: 2019-03-19


get scripts

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips



Comments For This Article




Thursday, April 29, 2021 - 2:03:05 PM - Jeff Niemann Back To Top (88626)
This all worked great for me when the path to the zipped folder and unzipped folder is mapped. However, This task fails when I use the relative path to the zip folder as I will need to do when I run this ssis package from a production server...

Is there a way to deal with zipped folders on a network drive?

Friday, December 4, 2020 - 6:24:33 PM - Herb Ray Back To Top (87883)
It's not necessary to install third-party software to work with ZIP files from SSIS. The Windows OS already "knows" how to handle ZIP files.

It's not obvious due to the fact that it's not really exposed explicitly in .NET object models. I keep a UTILS folder on my servers to store little utility bits that I use frequently. You can do this either with VBscript or with PowerShell. I've used both for this.

Here's a PowerShell utility that unZIPs files.


<#
First arg: Fully qualified ZIP folder path. Leave off the ".ZIP".
Second arg: Fully qualified destination folder.
Third arg: File name to extract. Leave this arg off to extract all files.
#>

$YesToAll = 16

$zipFolder = $args[0]

If ($zipFolder.SubString($zipFolder.Length - 4, 4) -ine ".ZIP")
{ $zipFolder = $zipFolder + ".ZIP"
}

$shell = new-object -com shell.application
$destinationFolder = $shell.NameSpace($($args[1]))

$zipObject = $shell.NameSpace($zipFolder)

ForEach($item in $zipObject.items())
{ If ($args[2] -eq $Null -or $item.Name -eq $args[2])
{ $destinationFolder.CopyHere($item, $YesToAll)
}
}


Wednesday, November 4, 2020 - 10:58:25 AM - Kevin Back To Top (87758)
Hi, thank you for posting this. Everything seems to work as instructed. However, the zip files created return the following message when attempting to open:

'Windows cannot open the folder. The compressed (zipped) Folder is invalid.'

I am able to use 7zip to unzip the files. Unfortunately this defeats my purpose, which is to prepare the data for SFTP.

Thank you!

Saturday, September 21, 2019 - 8:39:18 AM - Bhavesh Patel Back To Top (82530)

Hi RaR, refer to this article to achieve your goal.

http://www.techbrothersit.com/2013/12/ssis-how-to-unzip-zip-files-in-ssis-how.html


Wednesday, September 18, 2019 - 7:49:54 PM - RaR Back To Top (82501)

Hi, your post helped me big time! Thank you for your page.

I wonder, if I would like to use a parameter for the zip name, is it possible?

Thanks again and in advance.



download





Recommended Reading

Import UTF-8 Unicode Special Characters with SQL Server Integration Services

SSIS Expression Examples for Dates, String Concatenation, Dynamic File Names and More

Import Text and CSV Files into SQL Server Database with SSIS Script Task

Extract, Import and Migrate SSIS Project

Install SQL Server Integration Services in Visual Studio 2019














get free sql tips
agree to terms