Using SSIS to zip files and email the zipped files

Question

Post reply

Using SSIS to zip files and email the zipped files

Viewing 15 posts - 46 through 60 (of 81 total)

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

gjohnpulle SSC Enthusiast Points: 118 More actions · Answer 1

gjohnpulle

SSC Enthusiast

Points: 118

January 3, 2010 at 6:19 pm

#1098267

Hi Carolyn,

Really cool. Well written.

George

justpiyushmittal-1127780 Old Hand Points: 379 More actions · Answer 2

Hi Carolyn & Charley,

I am facing a similar problem. The task works well while executing directly from BIDS. But when i am trying to run it through a Sql Server agent job, it gets hanged out. I have checked the permissions on the folders for the account running the agent job and it has full permissions.

Any insight will be helpful.

Thanks,

Piyush

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

I usually run jobs using the SA account, and ensure that this service account has permissions on all the folders and is also an account that has permissions to send e-mails. You can add logging to your SSIS package to see whether you are getting any error mesages to help.

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

sandor_g SSC Veteran Points: 230 More actions · Answer 4

First of all, nice article.

I worked on a similar project before, and I also used an Execute Process task with WinZip to handle the zipping part. However, I didn't use VB script to manipulate the variables for the Arguments in the Execute Process task or for the Send Mail task. Instead, I evaluated some Variables as Expression and built them from other variables and text. This method doesn't require any knowledge of VB scripting and just as flexible.

Sandor

sandor_g SSC Veteran Points: 230 More actions · Answer 5

Did you save a copy to the server or are you running it from file? Do you have a proxy account set up to execute the package? Do you use any passwords in the package?

Sandor

justpiyushmittal-1127780 Old Hand Points: 379 More actions · Answer 6

I am running it from the file itself. Is this a problem..?

package does not use any credentials other then in the connections which I am supplying through the configurations file.

justpiyushmittal-1127780 Old Hand Points: 379 More actions · Answer 7

justpiyushmittal-1127780

Old Hand

Points: 379

January 4, 2010 at 10:50 am

#1098535

No Proxy account used.

sandor_g SSC Veteran Points: 230 More actions · Answer 8

If you run it from the file, you have to make sure that your SQLServerAgent account has permission to the path containing the package. Also this user account has to have pemission to the path in the zipping process. I recomend to use a proxy accont for the SQLServerAgent, that way you can easily set Windows permissions on any folder.

Sandor

justpiyushmittal-1127780 Old Hand Points: 379 More actions · Answer 9

Thanks for your reply, sandor.

owner account which is used to create\trigger the job has admin access on the box(windows and sql server both).

If I disable this purticular execute task, rest of the package works well.

sandor_g SSC Veteran Points: 230 More actions · Answer 10

Add a switch to your execute command to run the zipping in the background.

Sandor

sandor_g SSC Veteran Points: 230 More actions · Answer 11

justpiyushmittal-1127780, check out this link also. It has some useful tips.

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/da9c4117-8f43-4c61-a1b8-6214fdb97118

WayneS SSC Guru Points: 95460 More actions · Answer 12

clive-421796 (1/1/2010)
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 ).

Clive,

Set up a variable to build the day.

Set up a variable to build the filename, based on the previous variable.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 13

Below I listed my method using 7-zip which seaches the folder c:\temp\out\xand its subfolders for all files with *_20100104.xls , only includes it in the zip file,

20100104 is based on previous date.

Would like to automate the process of changing the date, but so far nobody seems to know how.

(I attached the 7-zip file, simply download, unrar/unzip with winrar, install).

(7-zip is freeware, you dont need to pay for it or for upgrades).

On page 7, I posted my winrar method, both are similar, but I prefer this 7-zip method.

Copy code below to Script Task of Visual Studio (BIDS):

Imports System.Data.OleDb

Imports System.IO

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

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 = "Customername" + FileDate + ".zip"

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

OutSubject = "Attached Zipped Files:- " + OutFileName

OutMessage = "Zip Successful"

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

End Class

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 14

Hi Wayne, my post is just below yours (Post #841757).

Can you have a look at it and reply with an example of variable for date.

RamaA SSC Veteran Points: 238 More actions · Answer 15

Thanks Carolyn,

what a brilliant Article.

i however have modified it a bit to use WinRar and so far it works perfectly.