Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»»

Using SSIS to zip files and email the zipped files Expand / Collapse
Author
Message
Posted Monday, January 4, 2010 10:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 14, 2013 9:54 AM
Points: 13, Visits: 73
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.
Post #841601
Posted Monday, January 4, 2010 10:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 14, 2013 9:54 AM
Points: 13, Visits: 73
No Proxy account used.
Post #841602
Posted Monday, January 4, 2010 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 3:15 PM
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
Post #841610
Posted Monday, January 4, 2010 11:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 14, 2013 9:54 AM
Points: 13, Visits: 73
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.
Post #841616
Posted Monday, January 4, 2010 11:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 3:15 PM
Points: 8, Visits: 66
Add a switch to your execute command to run the zipping in the background.

Sandor
Post #841626
Posted Monday, January 4, 2010 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 3:15 PM
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
Post #841635
Posted Monday, January 4, 2010 1:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:11 PM
Points: 6,604, Visits: 8,910
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
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
Post #841681
Posted Monday, January 4, 2010 3:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:40 PM
Points: 159, Visits: 454
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


  Post Attachments 
7z907.rar (5 views, 950.87 KB)
Post #841757
Posted Monday, January 4, 2010 3:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:40 PM
Points: 159, Visits: 454
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.
Post #841763
Posted Tuesday, January 5, 2010 4:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 22, 2013 2:25 AM
Points: 102, Visits: 151
Thanks Carolyn,

what a brilliant Article.

i however have modified it a bit to use WinRar and so far it works perfectly.
Post #841959
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse