Create a variable, Yesterday of type DateTime. Specify that it uses an expression and use the following expression. This provides a consistent reference point you can test against and if you disable the expression, allows you to specify a date for boundary/special case checking (like a leap year 2020-03-01)
DATEADD("DAY", -1, @[System::StartTime])
The next steps, especially if you're starting out, is to build the date parts in separate variables. It doesn't cost any extra to use lots of variables in your package and makes troubleshooting so much easier.
Add a new variable, YearString of type String.
(DT_WSTR, 4)datepart("YYYY", @[User::Yesterday])
That was easy.
Now we need to deal with create a zero, left padded string. Right now, your expression looks like it's trying to determine if day or month has 2 digits. I have a cleaner expression.
We're going to convert the day/month to a string and then prepend a zero to it. For Jan-Sep, we'll end up with a 2 character expression, Oct-Dec, we'll have a three character expression e.g. 011. For Day, similar bit where it's 01-09 or 010-031. We will then take the last two characters from the string. For the two character strings, it's a no-operation and for the three character variant, it gets us what we want.
Add Variable MonthString, as type string, to your package
RIGHT("0" + (DT_WSTR, 2)datepart("MONTH", @[User::Yesterday]), 2)
Add Variable DayString, as type string, to your package
RIGHT("0" + (DT_WSTR, 2)datepart("DAY", @[User::Yesterday]), 2)
Take a moment and look at your Variable collection. You can see that you have all the building blocks needed to properly construct your YYYYMMDD string. If something is wrong, it's small enough snippet to play with it. If not, break it up into smaller Variables.
Now that we have defined @Yesterday and then built YearString, MonthString and DayString off of Yesterday, all we have to do is bring it all together with the concatenation + operator
Back to the Variable well, creating @CurrentFileName of type string
"name_of_file_" + @[User::YearString] + @[User::MonthString] + @[User::DayString] + ".xml.gz"
Results in a value of name_of_file_20210216.xml.gz
Not addressed in this answer but things you should think about
What happens when the job doesn't run at all today (server fails horrifically, the data source is down, etc)? To pick up and process 2 days ago file, you would need to edit this package, run it through whatever change review process is applicable, deploy to prod, run it and then go back to the process yesterday file package.
That's not fun to type much less consider. You certainly aren't going to change the server time to trick the expression into being yesterday.
I am an advocate for passing the run date to the package (mechanism depends on how you deploy/run packages). In that situation, it's been my experience that it's a far easier bureaucracy fight to change the calling parameter (because no "code" has changed) than to get an emergency code change run through.
