Using SSIS to zip files and email the zipped files

Question

Post reply

Using SSIS to zip files and email the zipped files

Carolyn Richardson

SSCrazy Eights

Points: 8357
More actions
September 20, 2008 at 8:50 pm

#212901

Comments posted to this topic are about the item Using SSIS to zip files and email the zipped files
Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Viewing 15 posts - 1 through 15 (of 81 total)

You must be logged in to reply to this topic. Login to reply

Anipaul SSC-Insane Points: 24681 More actions · Answer 1

Anipaul

SSC-Insane

Points: 24681

September 22, 2008 at 1:40 am

#874820

Nice article ....

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 2

Hi Carolyn,

you have made it really simple. awesome!!! [:)]

Regards
Shrikant Kulkarni

0x30 SSC Veteran Points: 299 More actions · Answer 3

Or instead of breaking out into a external process, you can use the script task and SharpZipLib (http://www.icsharpcode.net/OpenSource/SharpZipLib/) or DotNetZip (http://www.codeplex.com/DotNetZip) C# Zip frameworks.

This is how we utilize zipping requirements in our control flows.

Idris Ogueboule SSC Enthusiast Points: 129 More actions · Answer 4

Good article Carolyn. Any idea about the maximum size of the files that can be zipped or the zipped file itself?

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 5

I think its up to 4GB, using 7 zip, but you should really check on the 7 zip site http://www.7-zip.org/. If the files you want to zip are bigger than this, you could always look at the products Grasshopper mentions.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Bk-384756 SSC Veteran Points: 231 More actions · Answer 6

Thanks for the great article, Carolyn!

Shouldn't it also be possible to use native Windows functions to compress and uncompress groups of files into .zip-compatible archives? NOT "compress" like the command line .exe or operating system file attribute, but similar to how I can view/create compressed .zip-compatible files from Windows Explorer.

Any thoughts?

Bk

Festeron SSC Eights! Points: 927 More actions · Answer 7

It's nice to see zipped files being used by SSIS, and most of the article was good, but surely there's a bug in this code:

If Len(strDay) = 1 Then

strMonth = "0" + strDay

End If

Liam Gavin-382521 SSC Veteran Points: 239 More actions · Answer 8

Liam Gavin-382521

SSC Veteran

Points: 239

September 22, 2008 at 10:08 am

#875120

Great article, thanks for sharing

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 9

Shouldn't it also be possible to use native Windows functions to compress and uncompress groups of files into .zip-compatible archives? NOT "compress" like the command line .exe or operating system file attribute, but similar to how I can view/create compressed .zip-compatible files from Windows Explorer.

Bk

I'm sure it's entirely possible to use native Windows functions to compress and uncompress groups of files into .zip-compatible archives. The article justs shows one method there are probably many ways to do the same thing. When I was tasked with zipping files, I could find no real world examples so had to figure it out myself, as its something thats likely to be needed again I thought I would document the way I chose to do it.

Festeron

Oops well spotted I will change later...to

If Len(strDay) = 1 Then

strDay = "0" + strDay

End If

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 10

I saw that too.

If Len(strDay) = 1 Then

strMonth = "0" + strDay

End If

probably should be

If Len(strDay) = 1 Then

strDay = "0" + strDay

End If

Bk-384756 SSC Veteran Points: 231 More actions · Answer 11

(grin) Maybe I should have been less obtuse...

What I was REALLY hoping for was "someone" to share how they'd done it WITH the Windows functions.

"OH GREAT SOMEONE, OUT THERE IN THE INTERWEB... Hear my request..."

Does the Interweb respond to sacrificial offerings?

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 12

Excellent article. Thank you.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

0x30 SSC Veteran Points: 299 More actions · Answer 13

What I was REALLY hoping for was "someone" to share how they'd done it WITH the Windows functions.

Well maybe this example is not with windows functions as they are API calls. But I can give an example using a .NET DLL Assembly!

1) Download SharpZipLib and install into the GAC (yes, has to be in the GAC. This is a MS decision!)

2) Add a script task

3) Edit the script

4) Add a reference to the DLL by right clicking on references and adding the reference

5) Initialise the object and use the assembly functions for Zip creation 🙂

We use package variables for the output dir and the name of the zip file (we add date info to this filename).

If you want, I possibly could write up a step-by-step article on how to achieve this in managed code.

Hope this makes sense 🙂

bc_ Hall of Fame Points: 3173 More actions · Answer 14

there is a free component that does this for you:

http://www.thejoyofcode.com/SSIS_Compress_File_Task.aspx

also, you can use an Execute Process Task and call winzip.exe:

http://dichotic.wordpress.com/2007/07/24/using-winzip-within-ssis/

[font="Arial Narrow"]bc[/font]