How do I SSIS WMI Event Watcher Query for a network folder?

Question

What I'm trying to do in SSIS is have a WMI Event Watcher Task which watches a folder for a file to be created, then does something with it. The primary part is the "watching the folder for file creation".

I have a network folder (full path): \\srvblah10\main\child\target\

All the sites I've gone to has this as an example:

SELECT * FROM __InstanceCreationEvent WITHIN 10
WHERE TargetInstance ISA "CIM_DirectoryContainsFile"
AND TargetInstance.GroupComponent = "Win32_Directory.Name=\"d:\\\\NewFiles\""

Since the folder is a network folder, I can't provide the physical disk letter. So is there a way to use a similar WQL query but for network folder paths as opposed to physical folder paths?

score 0 · Answer 1 · answered Jun 07 '14 at 20:01

0

You have to map the drive with a dos command: net use s: \srvblah10\main\child\target\ /user dotnetN00b Pa$$word

then you can the WMI Event Watcher Task to watch it.

answered Jun 07 '14 at 20:01

Joost

1,873
2
17
18

J. Grossman · Answer 2 · 2014-08-06T18:32:56.203

I was trying to do this for awhile, and finally gave up on trying to use the SSIS WMI Event Watcher task, and just wrote the equivalent in a Script task. The issue that was the challenge was getting the WMI Event Watcher to make the remote connection with specific user credentials that I wanted to obtain from a configuration section (not hard code into the package).

The second issue that was going to make not using a script difficult was simply translating the network share, into the local path name on the server, which the Event Watcher requires. You'll see from the scrip below, everything is accomplished with a minimal of effort.

Just an additional heads up, make sure to include the DTS.Variables the script uses in the ReadOnlyVariables (as normal). The code below requires three DTS variables, for example if you are trying to watch for files being dropped in the following location \copernicus\dropoff\SAP\Import, then you would set the variables as shown below:

User::ServerName - the hostname of the server where the share lives (copernicus)
User::ShareName - the name of the network share (dropoff)
User::ImportPath - the directory path of the directory to watch for new files in (/SAP/Import)

public void Main() 
{
string localPath = "";

    try
    {
        ConnectionOptions connection = new ConnectionOptions();
        connection.Username = "<valid username here>";
        connection.Password = "<password here>";
        connection.Authority = "ntlmdomain:<your domain name here>";

        ManagementScope scope = new ManagementScope(@"\\" + Dts.Variables["User::FileServerName"].Value.ToString() + @"\root\CIMV2", connection);
        scope.Connect();

        /// Retrieve the local path of the network share from the file server
        /// 
        string queryStr = string.Format("SELECT Path FROM Win32_Share WHERE Name='{0}'", Dts.Variables["User::ShareName"].Value.ToString());
        ManagementObjectSearcher mosLocalPath = new ManagementObjectSearcher(scope, new ObjectQuery(queryStr));
        foreach (ManagementObject elements in mosLocalPath.Get())
        {
            localPath = elements["Path"].ToString();
        }

        queryStr = string.Format(
            "SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE Targetinstance ISA 'CIM_DirectoryContainsFile' and TargetInstance.GroupComponent=\"Win32_Directory.Name='{0}{1}'\"",
             localPath.Replace(@"\", @"\\"),
             Dts.Variables["User::ImportPath"].Value.ToString().Replace(@"\", @"\\"));   // query requires each seperator to be a double back slash

        ManagementEventWatcher watcher = new ManagementEventWatcher(scope, new WqlEventQuery(queryStr));

        ManagementBaseObject eventObj = watcher.WaitForNextEvent();

        // Cancel the event subscription
        watcher.Stop();

        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (ManagementException err)
    {
        Dts.Events.FireError((int)err.ErrorCode, "WMI File Watcher", "An error occurred while trying to receive an event: " + err.Message, String.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
    catch (System.UnauthorizedAccessException unauthorizedErr)
    {
        Dts.Events.FireError((int)ManagementStatus.AccessDenied, "WMI File Watcher", "Connection error (user name or password might be incorrect): " + unauthorizedErr.Message, String.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }

}

How do I SSIS WMI Event Watcher Query for a network folder?

2 Answers2