Post reply

SSIS package Dynamic scheduling

  • May 22, 2012 at 1:03 am

    #392309

    Hi,

    We are having 3 different source system(Oracle, Webservice and Dataservice) and SQL server 2008 as the destination. We have 3 different packages pointing these source systems.

    In the existing senario we have scheduled the packages to run in SQL server agent to run daily at say 8.00 AM. But now, the packages should run as soon as the data is available from the source system(package run should be dyanamic and not static at 8.00 AM).

    Based on these source systems, I request you thoughts on how to implement the same. Please let me know if you need more details.

    Thanks

    Regards

    Jim

  • May 22, 2012 at 7:30 am

    #1491069

    I would probably create a master package that checked for the existence of the updated data and schedule it to run maybe every 5 minutes. If the data is there call the other package, if it is not then just have the package stop and wait for the next run.

  • May 22, 2012 at 4:35 pm

    #1491487

    You'll need to poll for the data but I would opt to do that work within the same package execution and not allow the package to exit. If the source data did not exist then I would have the package sleep for a time before checking again. Once the data is ready the package would proceed in processing it. Starting and stopping a package is expensive on multiple levels.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 23, 2012 at 12:30 am

    #1491561

    Hi,

    Thanks. Could you please let me know little bit more about the polling in SSIS? Considering Oracle, webservice and Dataservcie, which task should be used to do the polling.

    The last option that I have is to use the script component(I'm not very good at coding 🙁 ). Is there anything else I can use? Please suggest.

    Thanks

    Regards

    Jim

  • May 23, 2012 at 7:04 am

    #1491729

    You can access Oracle using an Execute SQL Task and you can access a web service using a Web Service Task. What is your definition of "Dataservice"?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 25, 2012 at 5:03 am

    #1493051

    What do you mena by "data is available"? Do you get load in terms of a flat file which you process. Please share more info. so as to facilitate us to give you a package skeleton.

    Raunak J

Viewing 6 posts - 1 through 5 (of 5 total)

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