Using SSIS to zip files and email the zipped files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Carolyn Richardson » Using SSIS to zip files and email the zipped...

77 posts, Page 8 of 8

«««4 5 6 7 8

Using SSIS to zip files and email the zipped files

Rate Topic

Display Mode

Topic Options

Author

Message

WayneS

Posted Wednesday, January 20, 2010 5:22 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 6,386, Visits: 8,283

WayneS (1/4/2010)

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.

Clive,
Sorry for the delay, I just stumbled across your request.
Okay, here's what to do:
1. Add a new variable. Call it "PriorDate". Set EvaluateAsExpression to True, and set the expression to "DateAdd("dd", -1, GetDate())"

2. Add a new variable. Call it "PriorDateYYYYMMDD". Set EvaluateAsExpresstion to True, and set the expression to:
(DT_STR, 4, 1252) DATEPART("yyyy", @[User::PriorMonth] ) +
RIGHT("0" + (DT_WSTR,2)MONTH(@User::[PriorDate]), 2) +
RIGHT("0" + (DT_WSTR,2)DAY(@User::[PriorDate]), 2)

3. Add a new variable. Call it "FileName". Set EvaluateAsExpression to True, and set the expression to:
"C:\temp\out\x\*_" + @User::[PriorDateYYYYMMDD] + ".xls".

4. In the Script task, Add the PriorDateYYYYMMDD variable as a ReadOnlyVariable. You can now use that variable inside your script task.

HTH,

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 #850928

kevin_nikolai

Posted Thursday, January 21, 2010 12:18 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:03 PM
Points: 141, Visits: 391

Hi WayneS,

Thanks.
Since the date will always be previous date, I added everything directly to the script task in SSIS package. Will create a copy of current SSIS package and implement your method for comparison.

Post #851503

shawn_hamzee

Posted Friday, September 24, 2010 8:17 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 11, 2012 1:38 PM
Points: 1, Visits: 149

Very helpful, thanks Carolyn.

Post #992820

nthetengm

Posted Monday, December 20, 2010 4:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:07 AM
Points: 3, Visits: 67

Hi,

Im getting an error requesting me declare dts in the scriptask. Any idea why this is happening?

Thanks,
T

Post #1037123

dsanghavi

Posted Monday, April 18, 2011 3:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, June 26, 2011 11:02 PM
Points: 1, Visits: 53

really very good and helpful....did my job... Smile

Post #1094801

khushboo.dudani

Posted Tuesday, November 01, 2011 9:36 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, December 10, 2012 7:16 PM
Points: 12, Visits: 83

Very helpful article! Simple and well-explained. Thanks for posting it!!!

Post #1199070

mushtaq308 97128

Posted Tuesday, April 02, 2013 4:08 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 5:39 AM
Points: 2, Visits: 10

Hi ,
Please help me telling what is the Output of Variable "OutExecutable",
I am doing the coding in C# and my Folderpath are different .
If some one could help telling the Output of "OutExecutable" then I could do my coding accordingly ??

Post #1437784

« Prev Topic | Next Topic »

77 posts, Page 8 of 8

«««4 5 6 7 8

Permissions