-1

I've built an SSIS package that runs on schedule weekly on Mondays. It looks for a .csv file that has a date at the end of the file name, which is 2 Saturdays ago from the date it runs (on Mondays). I need the variable to default to two Saturdays ago. Example: SSIS job runs on 7/9/2018, it needs to set it to 6/30/2018. SSIS needs to be runnable in case of failure, so if it has to be run again on, for example, 7/11/2018, it needs to default to 6/30/2018. This is a weekly job scheduled to run on Mondays.

I found a question posted similar to my need and the link is below for additional reference.

SSIS expression previous date without DateAdd()

JohnE
  • 1

1 Answers1

0

I just figured out how to format the date extension as 'mmddyyyy' inside Expression Builder for the variable. Below is my code.

RIGHT("0" + REPLACE((DT_WSTR, 30) (DT_DATE) DATEADD("Day", 7*(DATEDIFF("Day", (DT_DATE)0, GETDATE())/7-1), (DT_DATE)0),"/",""),8)

JohnE
  • 1
  • This seems to depend on the regional setting. With german settings, this results in `.06.2018` – Wolfgang Kais Jul 10 '18 at 21:25
  • WolfgangK, I just checked the formula and it turns out the date for 2 Saturdays ago is 07072018, however, in your formula it's returning 0772018. it's not including a leading zero in front of the date (7). Where in the formula would I implement this? – JohnE Jul 18 '18 at 20:42
  • You copied my last formula and got 0772018? That's astounding, because I get 20180707. What formula did you use? – Wolfgang Kais Jul 18 '18 at 20:58
  • It may not be your formula but if you evaluate the formula I posted you would get 0772018 – JohnE Jul 18 '18 at 22:02
  • Well, so there's another problem with YOUR formula. What exactly are you expecting me to do? Correct your own formula to finally make it a "solution" to your own problem? – Wolfgang Kais Jul 18 '18 at 23:10