locked
SSIS package gets run automaticatically after having indicator file RRS feed

  • Question

  • Hi Experts,

    I am new to SSIS and I have a requirement like below.

    Daily 6 times I will get a indicator file into particular location and based on availability of this indicator file my SSIS package has to run automatically. I should not use any SQL server Agent.

    I read some where by using WMI event watcher we can do this. Can some one help me is it correct or not.

    Here indicator file is indication of that source got loaded. From source table I have to read data from SSIS package and load into target table

    If it is not can guide me how to proceed this.

    Thanks in advance.

    Regards,

    Kumar


    • Edited by Kumarleau Wednesday, August 3, 2016 11:52 AM Small correction in description
    Wednesday, August 3, 2016 11:37 AM

Answers

  • Not manually, but there should be an automated process that starts the package again after it finishes, otherwise the WMI watcher is used up and by restarting arms itself up.

    https://sqlserverrider.wordpress.com/2011/10/10/file-watcher-ssis/ is the place to start, then once that WMI triggered set a flag to restart the package by an external job. That external job can be even a loop, or a scheduled to run bat file (with whatever frequency you need).

    In general, this is clumsy to monitor file system with SSIS IMO.


    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Navind Wednesday, August 3, 2016 4:32 PM
    • Marked as answer by Eric__Zhang Friday, August 19, 2016 12:26 AM
    Wednesday, August 3, 2016 1:36 PM

All replies

  • Hi there,

    yes, the WMI Filewatcher can do it, the only catch is that the package needs to be started again to continue monitoring.

    I did a few like that using the FileWatcher inside a Windows Service so the service would resume after the file has been processed.

    Both should work well in your undemanding scenario.


    Arthur

    MyBlog


    Twitter

    Wednesday, August 3, 2016 1:03 PM
  • Hi Arthur,

    Thanks for your reply.

    The package should run automatically whenever file available.  I should not run manually again.

    If it is possible, can you guide me how to do.

    Regards,

    Kumar

    Wednesday, August 3, 2016 1:12 PM
  • Not manually, but there should be an automated process that starts the package again after it finishes, otherwise the WMI watcher is used up and by restarting arms itself up.

    https://sqlserverrider.wordpress.com/2011/10/10/file-watcher-ssis/ is the place to start, then once that WMI triggered set a flag to restart the package by an external job. That external job can be even a loop, or a scheduled to run bat file (with whatever frequency you need).

    In general, this is clumsy to monitor file system with SSIS IMO.


    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Navind Wednesday, August 3, 2016 4:32 PM
    • Marked as answer by Eric__Zhang Friday, August 19, 2016 12:26 AM
    Wednesday, August 3, 2016 1:36 PM