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
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21608 Visits: 10652
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:TongueriorMonth] ) +
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
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
SSC Eights!
SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)

Group: General Forum Members
Points: 919 Visits: 523
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.
shawn_hamzee
shawn_hamzee
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 205
Very helpful, thanks Carolyn.
nthetengm
nthetengm
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 67
Hi,

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

Thanks,
T
dsanghavi
dsanghavi
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 53
really very good and helpful....did my job... :-)
khushboo.dudani
khushboo.dudani
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 83
Very helpful article! Simple and well-explained. Thanks for posting it!!!
mushtaq308 97128
mushtaq308 97128
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 112
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 ??
nikasmother
nikasmother
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
I can't seem to get the script task to work. Where exactly do I paste the code into what is already there. Thanks
Carolyn Richardson
Carolyn Richardson
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2881 Visits: 3537
Hi
You just replace the given contents for a new script task, with the contents given in the article and just replace your file locations and details.

Regards
Carolyn

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Neil.McKinnon
Neil.McKinnon
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 77
Hi Carolyn,

Do you know if its possible to run this using Microsoft Visual Basic 2008?

When I was looking at your code in BIDS it was showing DTS as unrecognised. Can you add a reference library or something to sort this?

Thank you kindly,

Neil
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