Skip to main content
Adding SSIS part of the question
Source Link
Delta
  • 113
  • 2

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.

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

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.

Source Link
Delta
  • 113
  • 2

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