SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SSIS to zip files and email the zipped files


Using SSIS to zip files and email the zipped files

Author
Message
Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1340 Visits: 751
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
Bk-384756
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 74
(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
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2406 Visits: 920
Excellent article. Thank you.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
0x30
0x30
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 80

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 Smile

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 Smile
bc_
bc_
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1686 Visits: 7333
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/


bc
Sajid-811795
Sajid-811795
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 164
This is really a great article with great simplicity.
Thanks for sharing.

Please keep it up
Miguel Costa
Miguel Costa
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 164
Hi,

Nice article.

I rather instead of using an Execute Process Task , that uses an external file, it's better (for me) to use the 7z instruction on the Script Task and put the 7z.exe in the Windows Assembly folder.


Regards.
Le Mauff Rémi
Le Mauff Rémi
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 56
Hi,

I agree whit this article, but when I develope this kind of package (Mail Task, FTP Task, Execute Process task), I prefere use the expressions andthe variables, evaluated as Expressions. I try to use the Script Task only for some task that SSIS can not do natively.

Sorry for my english Smile

Rmi
Adam Haines
Adam Haines
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6354 Visits: 3135
Bk (9/22/2008)
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

No, you cannot do this via Windows Compressed folders. You cannot do this because Windows Compressed folders does not have an interface or allow the use of command line switches . You could probably do some hacks to get it working, but it is outside the Windows built-in functionality. You will have to go with a non-Windows tool or API to accomplish this task.



My blog: http://jahaines.blogspot.com
Bk-384756
Bk-384756
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 74
NO Windows API to manipulate compressed folders? Ok then....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search