SSIS Compress File Task

Home > View Post

It was only a matter of time following my post the other day about System.IO.Compression, before I put this into an SSIS custom task. Well, here it is.

I think there is a good few use cases for such a task, particularly where you may want to archive your flat file following a package's execution.

Here's a quick example using the new task. We create gzip files for each IIS log file, and place them in a relative Archive folder.

SSIS flow with Compress File Task

Since the task only works on one file at a time, we place it inside a foreach container that is configured to loop through each *.log file in the IIS logs folder. The current file is then mapped onto a user variable called 'CurrentFile'.

SSIS flow with Compress File Task

We can use the UI of the Compress File task to setup exactly what we want to happen:

The Compress File Task's UI

First we choose what compression algorithm we want, with both GZip and Deflate being supported.

Next we setup the file operations, namely: whether an existing target file should be overwritten, whether the source file should be deleted at the end of the task and whether we want to validate the existence of the source file at design time.

Finally, we configure the source and target locations. Either can read its value from a variable by simply setting the Is_____FileAVariable and entering the name of the variable in the relevant box. Note that, In the example above, we use the CurrentFile variable as the source.

To add some real power, the TargetFile also supports two wildcards: %SOURCEPATH% and %SOURCEFILE%. We can use this to copy the path and name of the source file at runtime. Here we use it to create a file with the same name (plus a ".gz" extension) in an Archive folder inside the source folder. Remember, you could even use an expression for this property to timestamp the filename as demonstrated in my post about the Set Variable Task the other day.

I've put this together pretty quickly so be sure to let me know if you run into any problems or have any suggestions. Full Source and an msi installer is available for download.

PS - Apologies to readers who didn't subscribe to this blog for SSIS content. Stick with us, we do have more .NET stuff on the way :)

UPDATE: There was an update to the source and msi following a fix for this issue noted by Paul in the comments. The files linked on this post are the latest version.

UPDATE: The compress file task now supports decompression: SSIS Compress File Task now with added decompression.

Tags: SQL

Post By Josh Twist
8:20 AM
16 Jun 2006

» Next Post: SOA and more on why DataSets in web service interfaces are the spawn of Satan
« Previous Post: Save space with System.IO.Compression

Comments:

Posted by Paul Smith @ 30 Aug 2006 5:10 AM
Nice looking task and usefull, but, I get System.OutOfMemoryException when trying to gzip a file of 936,000KB, any pointers would be gratefully accepted ;-)
Paul

Posted by Josh @ 05 Sep 2006 8:54 AM
Hi Paul,

Thanks for your feedback. I'm on vacation at the moment but I will have a look into this when I get back. Thanks again..

Josh

Posted by KC @ 29 Sep 2006 7:29 AM
Thank you very much for this task. It will be greatly used. The GZipStream Class you use is limited to files 4gb and smaller. Do you know if Microsoft has any plans for increasing this file size limitation in the near future? Is there a similar class available from Winzip?

Posted by Jeff @ 12 Oct 2006 8:50 PM
Any ideas on how to create (or if there is already something out there) a transformation that will compress individual columns? So, I have a table with a key and 10 varchar fields - I want to create another table with the key and a single compressed column containing the data from those 10 fields. Possible?

Posted by Josh @ 13 Oct 2006 5:39 AM
Hi Jeff, not sure if there is anything out there that can already do this.

Why do you need to compress just one rows fields? Depending on the size of the fields, it's debatable how much gain you would make doing this at a row level, particular compared to the pain it's going to cause when it comes to scanning the compressed data :)

Sorry I can't be more help

Posted by Eric Belfort @ 08 Nov 2006 6:02 AM
Hi Josh. Thank you very much for making this available to all! This one is being really usefull to me because I need to build an SSIS project, but I know nothing about C# or VB.Net so I have no alternative but to use these specific SSIS tasks or command line tools, which are not as good an option. Would you by any chance have a SSIS task to do the opposite? Extract files from an archive?

Posted by Josh @ 08 Nov 2006 6:11 AM
Hi Eric,

Please drop me a line via our 'contact us' page.

Posted by Tony @ 27 Nov 2006 5:00 AM
Hi Josh. Nice Custom task. But what do I need to do if I want to compress, let's say, 4 files into 1 ".gz"-file?
Is this possible with your custom task?

Posted by Josh @ 27 Nov 2006 8:24 AM
Hi Tony,

Sadly not. The Compression classes don't support archiving of multiple files. You'd probably be better buying WinZip (or equivalent) and using their cmd line tool to achieve this.

Posted by Denis Alzoubi @ 26 Feb 2007 3:05 PM
Is there any extension that supports .zip files creation? I'd really appretiate it if there is...

Posted by Josh @ 26 Feb 2007 11:12 PM
Hi Denis,

Afraid not - there's no support for .zip built into .NET. If you want to use zip in SSIS, you could just by a copy of WinZip and use the cmd line version.

Josh

Posted by Alex @ 26 Apr 2007 5:26 AM
HI Josh

I've successfully installed this on my local PC but I cant install it on a server which I RDC onto? Are you aware of any issues relating to this?

Also, as im a newbie to SSIS how would I install the source code manually?

Posted by Josh @ 26 Apr 2007 11:59 PM
Hi Alex,

From memory, I think you only need to GAC the assembly if you don't intend to use it with Visual Studio. To use it with VS you need to drop the file in a certain folder - something like Program Files\Microsoft SQL Server\90\DTS\ or something. That maybe why the msi is failing if that folder doesn't exist - it's been a while since I've looked at this stuff so apologies for my vagueness.

You do have SSIS installed on the server?

Posted by twinsen @ 17 Dec 2007 7:49 AM
awesome, thanks!

Posted by Sachin Malik @ 24 Dec 2007 11:54 PM
I need to comress soem log files once in a week and want to automate this process.

Can u suggest me.

Thanks
Sachin

Posted by gary @ 05 Sep 2008 8:24 AM
.

Posted by Terry Smith @ 09 Sep 2008 11:32 AM
I would like to use this task in an SSIS package I have that generates large excel files then makes them available for download via links from an Intranet page. I have installed this locally just fine. My problem is the production platform is a server cluster. I have the jobs setup and running under a proxy. Do I have to install the task binary files on all the servers (C:\Program Files\Microsoft SQL Server\90\DTS\Tasks) directory for the SQLserver 2005 database to pick it up at run time? Right now I am getting an error stating the task failed to load. Any suggestions?

Terry Smith

Posted by Nasser @ 19 Nov 2008 1:32 PM
Hi, I was searching for compressing multiple files and hit this article. I could compress multiple files using for each loop. Thanks for the tips provided here.

Posted by Craig Botha @ 18 Aug 2009 4:43 AM
Thank you so much. This has been a HUGE help to me.

Posted by Jean @ 28 Aug 2009 8:04 AM
I would like to know how to pass the filename of a backup database task to the compress file task, any idea.
Thanks in advance

Posted by Justin @ 16 Nov 2009 6:27 AM
I can't seem to figure out where your installer is putting the files. I am not seeing the component available when trying to add it to the SSIS toolbox.

I looked in the various folders here (where other SSIS packages get installed):
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\

I also checked the x64 location.

Posted by Nagendra Prasad @ 25 Nov 2009 12:33 AM
Justin, Look inside the C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Tasks

Comments:

Post a comment: