Compare table datetime, SQL & SSIS

Question

I am trying to add a step in my SSIS job where it checks a table (FYI, so this table has only 1column which is a datetime data type that gets updated daily with a datetime from another table) whether it is greater than midnight or not. how do I setup this with the appropriate query.

This is the table name - ETLTimeCheck This is the only column in this table- EXEC_END_TIME

I started out with this idea:

SELECT...
FROM [dbo].[ETLTimeCheck] 
WHERE [EXEC_END_TIME] > DATEADD(D,0,DATEDIFF(D,0,GETDATE()))

This is suppose to return a midnight-> DATEADD(D,0,DATEDIFF(D,0,GETDATE()))

I know there must be an 'IF' statement inside my query. Can you please help me finish this?

Answer 1

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.