0

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?

2
  • Hi Michael and Welcome to dba stackexchange that indeed returns midnight but your question is not very clear. what do you want to achieve? check if exec_end_time is greater than midnight? Oct 21 at 7:16
  • Hi Marcello, thanks! So I am trying to add a step where it checks the row in that table is greater than midnight. so that table stores the time my ETL finishes, that's the primary job of this table just store the finish time...so, let's say this table is not updated and it still has yesterday's ETL finish time, then I will not run my job because the time is less than midnight today due to not being updated and still has yesterday's time. is that clear? with that being said I want the step to fail if the table is less than midnight and go to the next step if its after midnight. Oct 22 at 21:48

1 Answer 1

0

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.

3
  • Yes, I can get the midnight part resolved but how should I approach it if I want to do a check on that table if its greater than midnight and less than midnight... if it is greater than midnight i want to go to the next step of SSIS, if not Fail the step and report failure. Oct 22 at 21:50
  • I think you could achieve what you want to do by using a conditionnal split over the result of the previous query like so : sqlshack.com/ssis-conditional-split-transform-overview Just 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.
    – Delta32000
    Oct 24 at 5:59
  • I tried your way with conditional split but it didn't acceot any of my logics. However, I was looking for similar situation like a WHILE LOOP in SQL, that will definitely achieve it. Oct 25 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.