SSIS Date in Filename Expression

SSIS and Import/Export
1

I have a situation where I need SSIS to look for a filename with the date of the previous Wednesday. We have a fiscal week that ends on Wednesday and a file is generated in the format of abcd_yyyymmdd.txt where the date is that Wednesday marking the end of the fiscal week. The file is usually processed on the next Thursday but it could be processed on Friday or possibly even the following Monday. So I need the expression to check for the current day of the week and find the correct date for the previous Wednesday.

I know how to get the date into the filename it is just the logic for finding Wednesdays date that I am unsure of when creating an expression.

2

The expression below will give you the most recent Wednesday on or before the current date:

SELECT DATEADD(DAY, -DATEDIFF(DAY, 2, GETDATE()) % 7, GETDATE())

That will work under any any/all SQL Server settings.

3

I tried the following:

DATEADD("DD", -DATEDIFF("DD", 2, GetDate()) % 7, GetDate())

since I was getting an error when trying to use "DAY". The expression builder also wanted it in quotes.

I got an error message that says "the function datediff does not support the data type dt_i4" so I tried modifying it to

DATEADD("DD", -DATEDIFF("DD", 2,** (DT_I4)** GetDate()) % 7, GetDate())

Now I get an error that says "conversion from data type "DT_TIMESTAMP" to data type "DT_I4" is unsupported"

4

D'OH, sorry, I forgot, "2" is not valid as a date in SSIS. We must explicitly cast it to an actual date:

DATEADD("Day", -DATEDIFF("Day",(DT_DBTIMESTAMP) "1/3/1900",GetDate()) % 7, GetDate())

5

Thanks but I am still getting an error:

"Cannot convert expression value to property type."
"Cannot convert 'system.datetime' to 'system.string'.

If I try taking out the quotes it doesn't like the slashes. It also doesn't like single quotes.

Also when I try concatenating it with the file path and the rest of the file name I get another error:

Expression cannot be evaluated.

Attempt to parse the expression "@[User::FilePath] + "EFT_" + DATEADD("Day", -DATEDIFF("Day",(DT_DBTIMESTAMP) "1/3/1900",GetDate()) % 7, GetDate()))" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

I am not sure how I go about converting the entire date part of the expression so that it can be concatenated to variable and string.