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 Wednesday, January 20, 2010 5:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:36 PM
Points: 6,593, Visits: 8,874
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
Posted Thursday, January 21, 2010 12:18 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 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
Posted Friday, September 24, 2010 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 11:16 AM
Points: 1, Visits: 182
Very helpful, thanks Carolyn.
Post #992820
Posted Monday, December 20, 2010 4:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 9, 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
Posted Monday, April 18, 2011 3:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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...
Post #1094801
Posted Tuesday, November 1, 2011 9:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, April 2, 2013 4:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 5:28 AM
Points: 2, Visits: 59
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 »

Add to briefcase «««45678

Permissions Expand / Collapse