Changing global variable based on day of the week

I'm new to SSIS so please be gentle...

I'm creating a package that needs to go to an FTP site (FTP Task), download a file, unzip it and then process a series of table loads for the 12 text files that will be unzipped. My problem is that the zip file is a date (yyyymmdd.zip) which is normally the previous day of execution EXCEPT on Mondays when it would be the previous Friday's date. My thought is that IF (magic question) I could determine the day of the week in the SSIS package, I know that Tuesday-Friday is just a formatting exercise of getdate()-1 and Monday would be getdate()-3 but I can't seem to find a way (function?) that will allow me to determine the day of the week?

Thanks in advance!

Is that the only file on the site? If so it is easier to just download it using a wildcard *.zip. Then use a for each loop to process each file in the folder which will pick up the filename for you.

Even if it's not it's probably better to check all files on the site and process those which you haven't processed before.

What happens on bank holidays.

|||

Thanks for replying. Unfortunately it is not the only file (they keep them for archival purposes so there are hundreds). Is there an easy way to track which files I've processed (flag/switch) or are you suggesting a directory comparison or storing file names in a table somehow (not sure how to compare?)?

If there is no file, it just emails a failure notice and the process would be run manually as it is now (not the most efficient way but we can get to bells and whistles later).

You can disregard this question. I actually found the solution in a posting from "killerless" on 8/30/2006. He/she actually did pretty much the exact expression I needed to calculate the day. I love forums! :-)

|||

I would use a ForEachLoop conatiner with an expression in FileSpec property. The FileSpec property overrides the 'files' entry in the conatiner GUI. The expression should have the logic to get the right file name based on the system or execution date. I think sothing like this should work:

Datepart("dw", @.[User::CurrentDate] ) == 2 ?
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -3, @.[User::CurrentDate])),2) + ".zip"
:
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -1, @.[User::CurrentDate])),2) + ".zip"

It says someting like: if its friday; then substarct 3 days otherwise substract 1.

Put all the logic inside of the container. Let me know if you want the sample package.

|||It looks like my answer got too late |||Sorry! I do appreciate the effort!