locked
For loop executing SQL task every 15 minutes until flag changes RRS feed

  • Question

  • Hello,

    I have a working SSIS package. I need to add functionality that checks a column in a database every 15 minutes. I have a SQL task that populates a variable with the value from the database table, but I am not sure how to have it execute every 15 minutes until the value changes. I know how to set up the precedence constraints once I have the value, so that is not an issue. I am just having trouble setting up this functionality.

    Any help would be appreciated

    Dave


    Dave SQL Developer



    • Edited by DaveDB Wednesday, July 25, 2012 3:14 PM
    Wednesday, July 25, 2012 3:11 PM

Answers

  • I have the answer...

    I just dropped this code in and execute SQL task and it works great. It's really amazing how SSIS complicates things with the looping structures, sequence containers, etc. I understand now why Informatica is superior to SSIS.

    WHILE (select * from view) = 2
        BEGIN
           WAITFOR DELAY '00:15:00'
        END;


    Dave SQL Developer



    • Marked as answer by DaveDB Wednesday, July 25, 2012 7:02 PM
    • Edited by DaveDB Wednesday, July 25, 2012 7:28 PM
    Wednesday, July 25, 2012 7:01 PM

All replies

  • make a sql job scheduled after each 15 minutes, where in the first step you can check whether the value in the table is populated or not. ten execute the ssis package in the next step of that sql job.

    http://blog.sqlauthority.com/2011/05/23/sql-server-running-ssis-package-in-scheduled-job/

    reards

    joon

    Wednesday, July 25, 2012 3:22 PM
  • Great idea, but the SQL job that execute the SSIS package is going to run daily at 5AM. So unless I can execute a SQL job within a SQL job this will not work. Again, great idea!

    Dave SQL Developer

    Wednesday, July 25, 2012 3:29 PM
  • Hi

    Use a for loop before execute SQL task..

    DATEADD("mi", 15, @[System::ContainerStartTime]) > GETDATE() put this expression in assign expression, this will loop for every 15 mins..

    or

    Check this below link.. it may help you more..

    http://microsoft-ssis.blogspot.in/2011/04/pause-in-ssis.html

    Wednesday, July 25, 2012 3:29 PM
  • Ok, great but what goes in the Init and Eval expression?

    Dave SQL Developer

    Wednesday, July 25, 2012 3:34 PM
  • Great idea, but the SQL job that execute the SSIS package is going to run daily at 5AM. So unless I can execute a SQL job within a SQL job this will not work. Again, great idea!

    Dave SQL Developer

    it will depend on your requirement, say if you would like to populate the value and then immediately process the ssis package, then in the first step of the job, you run the logic to populate the value, and in the next step execute the ssis. all the burden for processing the things will be handled by sql engine. Or you can check the sequence container present within ssis.

    http://sql-bi-dev.blogspot.se/2010/06/sequence-container-in-ssis.html

    regards

    joon

    Wednesday, July 25, 2012 3:39 PM
  • Sorry guys, none of these work.

    Dave SQL Developer

    Wednesday, July 25, 2012 3:54 PM
  • Sorry guys, none of these work.

    Dave SQL Developer

    could you please elaborate a little more abt the requirement. You have a ssis package. You have a logic to populate a value in a table. Then you need to check that value every 15 min, from the ssis package or outside?

    If you can put the logic to populate the value inside the ssis package, then you can achieve both things at one go.

    regards

    joon

    Wednesday, July 25, 2012 3:59 PM
  • Sorry guys, none of these work.

    Dave SQL Developer


    Add a for loop that loops until that variable is populated. Add a Script Task within the foreach loop that waits for 15 minutes. See example two: http://microsoft-ssis.blogspot.com/2011/04/pause-in-ssis.html And add the Execute SQL Task that populates variable in that loop.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Wednesday, July 25, 2012 4:01 PM
  • Joon84,

    Thanks for you time.

    The package requirements are as follows...

    I am refreshing an SSAS cube and we have some preliminary ETL processes that need to run. There is a flag in one of our tables that tells us if the ETL process was successful (0), failed (1), or still running(2). First step of my process needs to check the status of that flag. If that flag = 2 I have to check every 15 minutes until the flag changes to either 1 or 0. Then I either leave the process (if it's 1), or execute the package (if it's 0). I have everything working with the exception of the 15 minute check. I also have an execute SQL task that populates a variable with value from the table.

    I hope this was clear enough. Sometimes it's hard explaining these things in chat.


    Dave SQL Developer

    Wednesday, July 25, 2012 4:16 PM
  • I have the answer...

    I just dropped this code in and execute SQL task and it works great. It's really amazing how SSIS complicates things with the looping structures, sequence containers, etc. I understand now why Informatica is superior to SSIS.

    WHILE (select * from view) = 2
        BEGIN
           WAITFOR DELAY '00:15:00'
        END;


    Dave SQL Developer



    • Marked as answer by DaveDB Wednesday, July 25, 2012 7:02 PM
    • Edited by DaveDB Wednesday, July 25, 2012 7:28 PM
    Wednesday, July 25, 2012 7:01 PM