I'm trying to build SSIS package that will be scheduled to run daily Monday through Friday. The first part of the package requires an FTP task to download files from a server with a variable path. The variable path is dependent on the day of week. For example:
I know that I can use (DT_WSTR, 1) DATEPART("dw", GETDATE() ) to get the numeric day of week and convert it to text. I can even get it as far as REPLACE( (DT_WSTR, 1) DATEPART("dw", GETDATE() ), "5", "thu" ) and then I'm stuck.
My question is, how can I conditionally replace "2" with "mon", "3" with "tue" and so on?
I appreciate any suggestions.