I guessed you could have multiple rows in your table but if not you would have to simplify these query. I guessed that midnight is 00:00:00 of the current day. If you want to get a kind of boolean result out of your query, you could achieve it in doing that way :
SELECT TOP(1)
CASE
WHEN EXEC_END_TIME > CONVERT(DATETIME,CONVERT(DATE,GETDATE())) THEN 1
ELSE 0
END
FROM dbo.ETLTimeCheck
ORDER BY EXEC_END_TIME DESC
If you have only a single row in your table you can delete the ORDER BY EXEC_END_TIME DESC
Then in SSIS you could use this query to split your data just like it is made on www.sqlshack.com/ssis-conditional-split-transform-overview/. Here, you can replace Washington and California by '1' and '0'. In your case you will have only one row so you can manage your "fail" as you want (send email, ...) in the other part of the if.