T.S. Eliot Quote

Where is the wisdom? Lost in the knowledge. Where is the knowledge? Lost in the information. 

T. S. Eliot.

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.

Book Review - Microsoft SQL Server 2008 Integration Services Problem-design-solution

It doesn't matter that this book relates to SQL 2008 because unlike most of the books on the market this book will not explain every element in the current version SSIS in dry detail.

Instead SQL Server 2008 Integration Services Problem - Design - Solution teaches you how to use the tool. It's not a book for beginners it's a book for intermediate and advanced users.

You may have built countless packages during your career and think you're pretty good. That may be the case but the authors too have many years of experience across multiple projects and they've learned what works best.

Even advanced SSIS developers can learn from this text. It might be the case that it validates your approach. if so then great. If not keep reading.

Each chapter covers a different problem. I particularly liked Chapter 2 - SSIS Management Framework Design from which I've used copied the audit schema and framework to use throughout my SSIS development. Chapter 6 - Data Cleansing Design is also good and we're building a generic ETL cleanser based on advice in this chapter for a single customer view implementation. Chapter 9 - SSAS Processing Architecture describes an excellent method processing OLAP cubes using XMLA. I'm really keen to get this implemented but time constraints on my project mean we're still in a drop and rebuild model.

In my opinion this is the best SSIS book on the market.

But it. Read it. Use it.