How do I compare the current date to a table containing columns with series of dates and use associated values as variables?

Question

Using SSDT 16.0.61908, SSIS 15.0.2000 and VS Community 16.3.4

Inside a data flow that will be a predecessor to other flows, I have a simple OLE DB Source that runs an SQL query against a table in a DB that returns 4 columns:

• PAYGROUP
• PERIOD START
• PERIOD END
• CHECK DATE

When I execute the package, I want the package to compare the run date to the most recently completed CHECK DATE value and set the corresponding PAYGROUP, PERIOD START and PERIOD END values as variables to use in the subsequent flows.

I've tried using derived columns but even then I couldn't get the syntax right. Furthermore if I am able to deduce the most recent check date and correlated values, I'm also not sure how to pass it to a variable that's called in subsequent flows.

Answer 1

For this I would use an ExecuteSql task.
1) Set the resultset to single row

2) Use the bottom part of the query below to select the correct row

3) In the resultset, map the columns to variables

DROP TABLE IF EXISTS #Payments;
create table #Payments 
    (
        PAYGROUP nvarchar(100),
        [PERIOD START] DATETIME,
        [PERIOD END] DATETIME,
        [CHECK DATE] DATETIME
    );

INSERT INTO #Payments 
VALUES('Group1', '2020-01-30', '2020-02-29', '2020-04-15')
    , ('Group2', '2020-01-30', '2020-02-29', '2020-04-16')
    , ('Group3', '2020-01-30', '2020-02-29', '2020-04-17')

-- Use this code in the SQL task, replace with real table

SELECT top (1)
    PAYGROUP,
    [PERIOD END],
    [PERIOD END]
FROM #Payments
WHERE [CHECK DATE] < Convert(date, getdate())
ORDER BY [CHECK DATE] DESC