We have a vendor right now that periodically requests a backup file FTP’d to their server. Unfortunately we do not run the Enterprise version of SQL Server to be able to compress the backups, and nobody wants to manually take a backup file and compress it on a regular basis.
Because of this I created an SSIS job that utilizes one of my favorite freebies, 7-zip, to uber-compress the backup file.
Here’s how:
First you’ll need to download the command-line version of 7-zip: http://downloads.sourceforge.net/sevenzip/7za920.zip
Then I extracted the 7za.exe file to c:\zip
Create a new SSIS project in Visual Studio.
Add an Execute Process Task.
Edit the Process Task as shown:
The arguments I’m using are: a -tzip (for zip) -mx9 (maximum compression) <output path and filename> <path to backup>
The executable is pointed to the command-line version of 7-zip.
Run the SSIS task. Will zip up the file (will take a while).
The AdventureWorks backup was 187k uncompressed.
After compression it was whittled down to 35k and ready to FTP.
