How to get current week monday's date in a variable in ssis package

Question

I have to get present week monday's date dynamically in ssis variable. can someone please help me on this?Able to get in SQL server but in SSIS not able to do so

Answer 1

You can use SQL to get monday's date and then set the variable with an execute sql task. Something like this:

WITH ids
AS (SELECT TOP (14)
           id = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 7
      FROM sys.columns)
   , alldates
AS (SELECT dates        = DATEADD(DAY, ids.id, CONVERT(DATE, GETDATE()))
         , week_num     = DATEPART(WEEK, DATEADD(DAY, ids.id, CONVERT(DATE, GETDATE())))
         , day_of_week  = DATENAME(WEEKDAY, DATEADD(DAY, ids.id, CONVERT(DATE, GETDATE())))
         , current_week = DATEPART(WEEK, GETDATE())
      FROM ids)
SELECT alldates.dates
  FROM alldates
 WHERE alldates.week_num = alldates.current_week
       AND alldates.day_of_week = 'Monday';