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 ).
Set up a variable to build the day.
Set up a variable to build the filename, based on the previous variable.
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:
riorMonth] ) +
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.
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