Very helpful code! However, I needed to create the filename with the date always 8 bytes. The sample chose 10/11 for a reason :-), if you use the code from January to September, and/or from the 1st to the 9th of the month, it either shortens the file name, or embeds spaces (for example - today is 2008911.txt, but I want 20080911.txt).
Anyway, I do not know how to make the expressions automatically return leading zeroes, so I modified the sample code with the following:
@[User::FileDestination] + (DT_STR, 4, 1252) YEAR (DATEADD ( "dd", 0, getdate())) + RIGHT ("0" + LTRIM ((DT_STR, 4, 1252) MONTH (DATEADD ("dd", 0, getdate()))), 2) + RIGHT ("0" + LTRIM ((DT_STR, 4, 1252) DAY (DATEADD ("dd", 0, getdate()))), 2) + ".TXT"
The basic logic is, take the RIGHT two characters of the resulting expression. When the month is 10 - 12, you'd get 010, and the RIGHT two of that is still "10". If the month is " 9", the result is "09" and you still get what you want.
If anyone has an easier way, please let me know. I thought that I could just use the following:
CONVERT(CHAR(8), GETDATE(), 112) but the TransactSQL expressions do not work in the SSIS Expressions builder.