none
Running the package whenever the source file is placed - Is it possible in SSIS?

    General discussion

  • Hi,
      We have 20 packages that have been scheduled to run every Monday. The source file is Text as well as Excel files. Now, in addition to running on the scheduled time, we wants the package to  run whenever they place the source text and excel files in some specific folder.
      is it possible in SSIS 2012? Do we have any option? This has to be run both in QA and PROD - so not sure whether we can use some third party tool. However, please suggest me what are the options available..


    Thanks in advance!
     
     
    Tuesday, September 17, 2013 3:17 PM

All replies

  • The solution I typically recommend is writing a Windows Service encompassing the .Net FileWatcher

    once the FileWatcher detects a file arrival it logs this even to a table (acting as a queue).

    The SSIS package can then be started from within the very Windows Service using a bat or start_process or a SQL Agent job (sp_start_job <name>).

    I have implemented two of these, it took half a day to get there the 1st time. 

    It works better that watching for files in a looping package with a WMI event task.


    Arthur My Blog

    Tuesday, September 17, 2013 3:41 PM
  • Great! Can you please tell me the steps for doing that? or any website where i can get the .net code as i am not into .Net code..it would be really great..
    Thursday, September 19, 2013 12:06 AM
  • Great! Can you please tell me the steps for doing that? or any website where i can get the .net code as i am not into .Net code..it would be really great..
    +1
    Thursday, September 19, 2013 12:48 AM
  • I've done the same thing before using this custom task: http://www.sqlis.com/post/file-watcher-task.aspx

    worked fine


    Adelaide, Australia

    Thursday, September 19, 2013 7:02 AM
  • Great! Can you please tell me the steps for doing that? or any website where i can get the .net code as i am not into .Net code..it would be really great..

    Sure, step

    1: File Watcher in a Windows Service http://www.codeproject.com/Articles/18521/How-to-implement-a-simple-filewatcher-Windows-serv

    2: Running SSIS package programmatically


    Arthur My Blog

    Thursday, September 19, 2013 2:39 PM
  • Great..thank you so much for both of you guys!
    Thursday, September 19, 2013 9:52 PM
  • You can also have a package which runs indefinitely and use WMI watcher task in this package to wait for the file.

    You can fined a sample in this tread:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ff7c2067-b264-403e-a539-2efc51901018/wmi-event-watcher-task-not-sensing-arrival-of-a-file

    Friday, September 20, 2013 1:46 AM
  • Another way to approach this problem...

    1. Create a Master SSIS package with FOR Each loop. 

    2.FOR EACH loop will be checking a file location and when there is a file it will execute the child packages to process files.

    3.Create a SQL Agent job to execute the master package

    4.Schedule the SQL agent job to execute as per business requirement.

    --Please note you need to create DB credential and SSIS executor account to invoke SSIS package from SQL agent job. 

    Friday, September 20, 2013 4:26 AM
  • It can be easily done by using SQL jobs..

    All that you need to do is defining a alert after creating a job.

    Defining an alert is quite straightforward and can be configured by right clicking on the ‘Alerts’ FOLDER in SQL Server Agent and select ‘New Alerts’

    create a WMI Event Alert called Testing Alert with the following query.

    The namespace mentioned here similar to the namespace we give in c#.net
    The following query picks up any event which is created due to file transfer of Test .txt to the folder c:\TestFolder\
    SELECT * FROM __InstanceCreationEvent WITHIN 1 WHERE TargetInstance ISA 'CIM_DataFile' AND TargetInstance.Name = ‘c:\\TestFolder\Test.txt’

    Friday, September 20, 2013 6:48 AM
  • Hi,

      Thanks a ton guys for your valuable inputs!

    Initially I tried what Arthur mentioned - there are 2 steps

    Step 1: I am done till build the project. After that, the article says something like below, which i don't understand as i have never worked on C# anytime

    // To install the newly created service, you must use the .NET Framework InstallUtilprogram. I have included two batch files within this project- one to install the service, and another to UNinstall it. Each can be reused. Simply replace the PROG variable with the name of the service that you are installing or uninstalling.//

    Step 2:

    Running SSIS package programmatically -- this has 5 approaches . Not sure which one to choose.

    Can you guys help me to complete the step 1 and step 2?

    thanks !

    Friday, September 20, 2013 6:15 PM
  • 1: You just literally need to replace the PROG with your service name

    2: I chose option 3 (sp_start_job stored proc running my Agent job in which the SSIS step is)


    Arthur My Blog

    Friday, September 20, 2013 6:20 PM
  • thanks Arthur...last question, can you please tell me  what does this mean -"need to replace the PROG with your service name"? where would be the PROG and how can i replace?
    Saturday, September 21, 2013 8:13 AM
  • Hi,

       I tried Kingxxx1's approach. I am using SSIS 2012.

    These are the steps i did to understand his approach

    1) Created a simple package to load a small text file into a Table.

    Source file Folder : C:\Users\vsk\Desktop\TestFolderForFileWatcher

    TextFile: TestSourceFileForFileWatcher.txt

    2) Deployed the pkg in the SSISDB catalog

    3) created a SQL server job

    4) Created a WMI Event Alert and enabled it.

    Namespace by default:\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER

    Query:

    SELECT * FROM __InstanceCreationEvent WITHIN 1 WHERE TargetInstance ISA 'CIM_DataFile' AND TargetInstance.Name = ‘C:\\Users\\vsk\\Desktop\\TestFolderForFileWatcher\\TestSourceFileForFileWatcher.txt

    I deleted the text file and then put it again, hoping that the package will run after detecting the file. But it is not getting executed.

    I do not know anything about .Net, or __InstanceCreationEvent event. Now, can you please tell me whether i am missing anything. Should I write some c# code somewhere to enable smoething.It would be great if you provide me some detailed information!

    thanks guys!

     

    Monday, September 23, 2013 5:59 AM
  • With the WMI FileWatcher you need to run the package in an infinite loop. Which still leaves some room for a potential to miss the package, like I said, not advocating it to anyone.

    I guess the example I gave you is a little hard to follow, how about you watch how to create a Windows Service: http://www.youtube.com/watch?v=uM9o8GsO_u4

    You then still can borrow the code from my example that covers the creation of the FileWatcher and then you must be all set with your task.


    Arthur My Blog

    Monday, September 23, 2013 2:18 PM