Using SSIS to zip files and email the zipped files

Question

Using SSIS to zip files and email the zipped files

Viewing 15 posts - 31 through 45 (of 81 total)

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

mike.clayton007 Old Hand Points: 302 More actions · Answer 1

Its throwing an error 'Unknown option "tzip"' . But when I click ok in the mesage box. Then its archiving the files to RAR.

Any thoughts !!

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

Can you post the command you are running?

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

M Chabot SSCrazy Points: 2511 More actions · Answer 3

mike.clayton007 (11/10/2008)
I am new to SSIS and VB. The article was very usefull. But I was unable to run the package using WINRAR.
Will this work for WINRAR (may be I need to change the arguments).
Any help would be greatful.

Since WinRAR is a different program than 7zip it would have different command-line arguments, but it should work once you get the arguments correct. The correct command-line arguments would be in the WinRAR manual or on the WinRAR Web site.

mike.clayton007 Old Hand Points: 302 More actions · Answer 4

I used:

" a """ + OutFolder + OutFileName + """ """ + InFolder + "*." + InFileType + """"

Its working fine now. But is there anyway that i archive only the files into my destination folder. Right now it also has the source folder structure or path.

Thanks

clou-951938 SSC Enthusiast Points: 138 More actions · Answer 5

Thanks for sharing the knowledge. This could be a good sulotion to archive the processed raw files when you have a daily procedure that uploads the raw flat files to the SQL Server tables. I tried it and it works fine in BIDS. But when I try to run the package from a SQL Server Agent job, it does run to the end successfully but nothing happen! No file has been add to the zip. I have a 64bit 7Zip installed on my 64bit machine and my SQL Server 2005 is 64bit as well.

Anybody have it run successfully from a SQL Server Agent job? Please share your experience.

Thanks,

Charley

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

There is absolutely no reason why this shouldn't work, have you double checked all the variables and connections?

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

clou-951938 SSC Enthusiast Points: 138 More actions · Answer 7

Hi Carolyn,

My test is very simple and it contains only a script task and execute process task. the script task is used to setup a string for the arguments of 7Zip.exe. The execute process task is used to run the 7Zip.exe with the maked up argument string. And that's it. There is no connection required. It runs perfect in BIDS. For some unknown reason, it doesn't work properly from SQL Server Agent job. Actually, the job runs successfully without error at all. But the zip file didn't be created and no files had been zipped in of course.

Thanks,

Charley

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

Try adding some logging on the job when it runs to get some more information its a bit difficult without seeing the job you've set up to tell what's happening

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

clou-951938 SSC Enthusiast Points: 138 More actions · Answer 9

Hi Carolyn,

I get it worked out! Actually, it's a credential issue. The account used to run the SQL Server Agnet don't have a proper access to the file system on the server. I create a proxy with a proper credential for the Agnet job. It resolves the problem.

Thanks,

Charley

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

Thats great news glad you've got it working.

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

aroravishal22 Valued Member Points: 55 More actions · Answer 11

Hi,

I am getting the following error. I was able to run this a few days back, but now it is giving this error.

[Execute Process Task] Error: In Executing "C:\Program Files (x86)\7-Zip\7z.exe" " a -tzip "\\srvr1\myfile_1209.zip" "\\srvr1\myfile.csv"" at "C:\Program Files (x86)\7-Zip", The process exit code was "2" while the expected was "0".

any help in this would be great.

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

Try the following:

1) Can you access the server?

2) Can you access the share and verify write access is still enabled?

3) Can you run the command and verify that it executes properly?

4) Now test runing under the account that is used for your SSIS tasks.

This will help you narrow where the problem lies. eg: permissions on the server, smb issues, network issues

gabriel.anton Grasshopper Points: 19 More actions · Answer 13

aroravishal22 (12/28/2009)
Hi,
I am getting the following error. I was able to run this a few days back, but now it is giving this error.
[Execute Process Task] Error: In Executing "C:\Program Files (x86)\7-Zip\7z.exe" " a -tzip "\\srvr1\myfile_1209.zip" "\\srvr1\myfile.csv"" at "C:\Program Files (x86)\7-Zip", The process exit code was "2" while the expected was "0".
any help in this would be great.

Hi,

You must have installed 7 zip on your server, and be carrefull, the sample has been made on X64 platform. If you have x32 platform you need to change the path which is pointing to 7zip. Try to use same machine for testing. (replace \\srvr1 with a local path)

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 14

Hi Carolyn, I used your article: Using SSIS to zip files and email the zipped files

By Carolyn Richardson, 2010/01/01. It works like a charm.

( http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64028/ ).

Below is the code I used to include only the file of previous day

(only want to zip all files with date of previous day):

------------------------------------------------

OutExecutable = " a -tzip -ir!c:\temp\out\*_20091208.xls """ + OutFolder + OutFileName + """ """ + InFolder + "*." + InFileType + """"

How can I automate this so that I dont have to edit the Script Task every day and specify the date of previous days files to include, in this case 20091208 ( c:\temp\out\*_20091208.xls ).

m.ciesiensky SSC Enthusiast Points: 111 More actions · Answer 15

to clive. . .

We had to modify the script to account for running daily. Also, as we already owned a copy of WinRAR, that is what we used. Also, instead of emailing the zip file, we are emailing a confirmation with filename. The actual zip is copied to offsite, mirrored backup servers nightly using ROBOCOPY. We've been using this successfully for more than 16 months now.

Public Sub Main()

Dim InFolder, InFileType, OutFileName, OutExecutable, OutSubject, OutFolder, OutMessage As String

Dim FileDate, strDay, strMonth, strYear As String

InFolder = Trim(CStr(Dts.Variables("User::InFolder").Value))

OutFolder = Trim(CStr(Dts.Variables("User::OutFolder").Value))

InFileType = Trim(CStr(Dts.Variables("User::InFileType").Value))

strDay = Right("0" + CStr(Day(Now)), 2)

strMonth = Right("0" + CStr(Month(Now)), 2)

strYear = CStr(Year(Now))

FileDate = strYear + strMonth + strDay

OutFileName = "dbArchive_" + FileDate + ".rar"

OutExecutable = " a -m5 -r """ + OutFolder + OutFileName + """ """ + InFolder + "*_" + FileDate + "*." + InFileType + """"

OutSubject = "Daily DB Backup: " + FileDate

OutMessage = "Backup Successful. filename: " + OutFolder + OutFileName

Dts.Variables("User::OutExecutable").Value = OutExecutable

Dts.Variables("User::OutFileName").Value = OutFolder + OutFileName

Dts.Variables("User::OutSubject").Value = OutSubject

Dts.Variables("User::OutMessage").Value = OutMessage

Dts.TaskResult = Dts.Results.Success

End Sub