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
justpiyushmittal-1127780
justpiyushmittal-1127780
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 77
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
justpiyushmittal-1127780
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 77
No Proxy account used.
sandor_g
sandor_g
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 66
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
justpiyushmittal-1127780
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 77
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
sandor_g
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 66
Add a switch to your execute command to run the zipping in the background.

Sandor
sandor_g
sandor_g
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 66
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
WayneS
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9852 Visits: 10572
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

kevin_nikolai
kevin_nikolai
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 523
Below I listed my method using 7-zip which seaches the folder c:\temp\out\x\
and 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
Attachments
7z907.rar (10 views, 950.00 KB)
kevin_nikolai
kevin_nikolai
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 523
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
RamaA
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 239
Thanks Carolyn,

what a brilliant Article.

i however have modified it a bit to use WinRar and so far it works perfectly.
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