ssis package to name a file in the format yyyymmdd_filename.txt
Posted on 2012-09-05
I have the following expression
@[User::FileLocation] + "\\" +
(DT_STR,4,1252) DatePart("yyyy",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DAY( DATEADD("d",-1, GETDATE()) ),2) +
I have set up an expression in my package, so that all files will follow the format
yyyymmdd_PatientUpdates.txt and the variable FileLocation contains the location where the file will be stored.
However, there is a problem with my logic, and was wondering if someone could help out here. I always need the value for yyyymmdd to be one day less than the current day. So if the day is today, then the format would be 20120904 and so on.
The problem occurs when I come to the first of the month, so the current day was Sept 1st, I would need the value to be 20120831, but it came out as 20120931. I can see why it happened that way, what I am not sure, is how I can make it come out the correct format each time.
I am not totally up to date on SSIS and expressions, so any help you can give would be appreciated, thanks.