A 7-Zip Archiving Task for my SSIS Package Template

I use an SSIS package template to speed up project delivery. 7-zip is my archiving tool of choice because it provides encryption and password protection in addition to good compression. It has all the scripting options you'd expect in the command line version which makes it easy to use with SSIS. Documentation for command line scripting can be found here.

I call 7-ZIP from an Execute Package Task (EPT). This requires the 7-Zip executable to be present on the server where the SSIS package executes. The standard 7-Zip executable (7z.exe) doesn't work as a command line tool. Instead you need to use the 7za.exe. Both can be downloaded here.

In my SSIS Template I have a preconfigured EPT task with expressions constructed to build the complex strings required by the command line tools from SSIS variables. These variables can be set for simple recurring packages or with minor amendments populated from configuration values.

Configuring the Task

I always name each task using a standard acronym for the task followed by a brief description. This makes debugging much easier both during development using the package explorer pane and once the package is live as all the packages created using my template log to an audit schema. This audit can be invaluable when searching for a failure within a complex process. In addition, the log allows reporting on the duration of each step which aids performance tuning.

The screenshot below shows the general pane for the Execute Process Task used in this example.

Next follows the all important Process pane. This is where configurations are set. Or is it? With complex strings required as command line parameters it is better to configure the task dynamically using expressions and variables.

In this version on my package template I set only two values, the Arguments and Working Directory. I probably should have set the Executable location via a variable as well but I guess that'll wait for the next version.

The 3 string variables used are;

• [User::ZipFileName]

• [User::ZipFileLocation]

• [User::ZipPassword]

The most complex expression is that which sets the commend line arguments, shown here;

"a -tzip " + @[User::ZipFileName] + "_" + (DT_STR,4,1252)YEAR(GETDATE()) + RIGHT("0" + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT("0" + (DT_STR,2,1252)DAY(GETDATE()),2) + ".zip *.csv -P" +  @[User::ZipPassword]

And this results in the following string; 

a -tzip DataExport_20130822.zip *.csv -PPa55word!

In Summary

This is a useful addition to my SSIS package template which I use regularly. I don't believe in reinventing the wheel, instead I prefer simple modular solutions I can combine to make more complex packages. 

The time taken to establish a template is well worth the investment. I'm often very busy in my role and I prefer to focus my limited time and brain cycles on higher level problems than configuring an SSIS task. I recommend building your own template or using mine which I'll be sharing shortly.

Check out my WinSCP configure task also used as part of my SSIS package template.