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
Author - SQL Server T-SQL Recipes