How to check the data in an SQL table in regular intervals

Question

How to check the data in an SQL table in regular intervals

Sahin Kaya

SSC-Addicted

Points: 461
More actions
September 7, 2008 at 2:54 pm

#131164

Hello,
I would like to check the status of a long running calculation in SQL, which is updated as a flag at a column of a table: When the calculation is continuing the value of that column is say "0" and whenever it finishes, it is updated as "1".
I want to check the value of that column in regular intervals (say every minute) by means of a task/container in SSIS, and whenever it reads "1" meaning "the calculation is finished", I want it to move the next task in the SSIS (not before it reads "1").
I tried to use the loop containers in SSIS for this purpose but it was not successful since I could not see any option related to "time" at that that type of containers.
Any help would be appreciated.
Thanks in advance.
Best regards,
Sahin.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

If you read the table using WITH (NOLOCK), you will be able to see the progress of the updates. Of course, that requires a separate parallel process.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sahin Kaya SSC-Addicted Points: 461 More actions · Answer 2

Thank you Jeff.

But my question is how I can run that query in every 1 minute repeatedly in SSIS until I see that value is updated, and allow SSIS to move to the next task in the sequence ONLY AFTER this update occurs?

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

You can use a loop container and a SQL Command that calls WAITFOR to wait for some time and poll again.

You could also do that entire operation in a stored procedure - loop, poll, and wait and have the procedure return when the process is ready to move on - this would mean you just have to call the procedure in SSIS and wait for it to complete.

I don't know exactly what you are trying to do, but I would bet it is not a good idea. You have some long running process that - part way through - updates some records. You are trying to wait for that process to get to that point before your package moves on. What happens if that process rolls back? A better approach would probably be to split up the process.

Sahin Kaya SSC-Addicted Points: 461 More actions · Answer 4

Thank you Michael.

I need to use such a process to automatically start my SSIS part after a certain program (that I cannot have any interject on it) finishes its job, and signals that by updating a certain table.

I like your idea of having all that waiting loop in a stored procedure. I would very much appreciate if you could give a simple example showing the statements that I need to use in such a code.

Many thanks in advance.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

Ok, it's not tested, but it would be something like this:

[font="Courier New"]CREATE PROCEDURE dbo.spWaitForProcess

@intvlsec INT

, @timeoutsec INT

AS

DECLARE @totalsec INT

, @delay VARCHAR(14)

SET @totalsec = 0

SET @delay = CONVERT(VARCHAR,DATEADD(Second,@intvlsec,0),114)

/* Modify select statement below */

WHILE (NOT EXISTS(SELECT * FROM MyTable (NOLOCK) WHERE X=Y) AND @totalsec < @timeoutsec)

BEGIN

WAITFOR DELAY @delay

SET @totalsec = @totalsec + @intvlsec

END

IF @totalsec < @timeoutsec

RETURN(0)

ELSE BEGIN

RAISERROR('Timeout Expired.',16,9)

RETURN(1)

END[/font]

Sahin Kaya SSC-Addicted Points: 461 More actions · Answer 6

Thank you very much Michael. I tested it; it is perfectly running. 🙂

wildh SSCarpal Tunnel Points: 4370 More actions · Answer 7

Another way you could try this is (in the control flow) to set up a expression. To do this you would drag a constraint from task to another and double click it. In the "precedence constratint editor" change the evaluation operation from contraint to "expression and constraint" or one of the other options and then type in the expression (a true/false result), this could also be a user variable based on a previous variable but by the sounds of your requirements the variable option might not be best in this situation.

Hope this gives you another option to investigate.

Sahin Kaya SSC-Addicted Points: 461 More actions · Answer 8

Thank you Wildh. I will try that option also.

Regards,

Sahin.