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
SSC Eights!
SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)

Group: General Forum Members
Points: 904 Visits: 740
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
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

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

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
0x30
0x30
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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_
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 Visits: 7322
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 (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

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

Please keep it up
Miguel Costa
Miguel Costa
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3166 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
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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