September 7, 2008 at 2:54 pm
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.
September 7, 2008 at 5:22 pm
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
September 8, 2008 at 1:45 am
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?
September 8, 2008 at 4:47 am
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.
September 8, 2008 at 7:35 am
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.
September 8, 2008 at 7:47 am
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]
September 8, 2008 at 8:40 am
Thank you very much Michael. I tested it; it is perfectly running. 🙂
September 10, 2008 at 1:47 am
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.
September 10, 2008 at 4:26 am
Thank you Wildh. I will try that option also.
Regards,
Sahin.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply