sql server ssis wmi event watcher

Question

I want to create a SQL Server SSIS package where I can watch a folder and once I have all (20 files) the required files I want to execute a sql statement. The files may come at different times and sometime they will be in csv and sometime they can come in zip. I know ssis has a wmi event watcher task but I’m not sure how I can specify to look for all 20 files. I guess I want wmi event watcher to look into that folder every 30 minutes and once it sees all the files move to the next step (execute sql task). Can someone tell me how I can specify the file name in wmi event watcher task? Thanks.

yes, there's also text files and there will be csv files that I don't want the wmi to look for — user20907

Sam Sam · Accepted Answer · 2013-06-13T20:42:29

This article seems relevant to your plan. You need to create the proper WQL code.

http://blogs.technet.com/b/heyscriptingguy/archive/2007/05/22/how-can-i-monitor-the-number-of-files-in-a-folder.aspx

("ASSOCIATORS OF {Win32_Directory.Name='C:\Logs'} Where " _
            & "ResultClass = CIM_DataFile")

I'm not sure how that will behave in the WMI Event watcher though. Have you looked at the docs for the SSIS task?

Here is a more step-by-step approach:

http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi.html

Some good points there, even if it doesn't address the pesky 20 file requirement.

You could also have a powershell script on the server monitor the files and then chuck them into a subfolder when they are all there, which SSIS would be monitoring.

Here is a doc page showing how to specify one file:

http://msdn.microsoft.com/en-us/library/windows/desktop/aa394594(v=vs.85).aspx

With that, I'm sure you could set up a chain of WMI checks in your SSIS package.

sql server ssis wmi event watcher

1 Answers