ssis package to name a file in the format yyyymmdd_filename.txt

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.
Who is Participating?
JDEE8297Author Commented:
I think I just figured it out

"C:\\Temp\\ErrorCodes\\" + (DT_WSTR,4)YEAR(DATEADD("dd", -1, GETDATE())) + "-"
    + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE())), 2) + "-"
    + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())), 2) + ".txt"

with some modifications for my needs, this should do it, unless someone comes up with a better way of doing this. I will give this a go.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.