WMI Event Watcher - Pointing Directly To A Machine Name
Nov 27, 2006
Hi,
I am using a WMI Event Watcher task to watch for files dropped into a directory over a network drive.
It seems to work fine when it is pointed to a physical drive name, but not when I use a machine name.
I.e.
This works -
SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name="C:\\temp\\folder1\\folder2\folder3""
This fails -
SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\\\\machineName\\folder1\\folder2\\folder3"
The failing code does not throw any errors (i.e. SSIS thinks the WQL syntax is correct), but it doesn't pick up any dropped files.
Am I doing something incorrect with the syntax, or must I use a physically mapped drive letter?
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file. The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30 WHERE targetinstance isa 'CIM_DataFile' AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message. However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
i would like to know how i can retrieve the file path+name of a file detected by the WMI Event Watcher task, so that I can assign this to a variable for an Import task
I want to watch a directory 24 hours a day to launch SSIS packages as files arrive. There are several options I have found to accomplish this:
Use the WMI Event Watcher task in SSIS to launch the packages as files arrive. Leave the package with the WMI Event Watcher task running all day long everyday Create a Windows service that uses WMI to detect file arrival and launch packages Schedule SQL Server Agent to run the package when a WMI event is raised
What is the best practice? Are there better approaches that I have not listed?
I have an SSIS Package to get the data from an Excel Source. For that I am using WMI Event Watcher to find when the file drops into a specific folder. It is working fine when I check any folders in the local machine.
We have a separate WebServer and Database Server for our application. The application is deployed in the Web server and the SSIS package is deployed in the Database server.
Since the files are dropped through application, it is dropped in the Webserver. I have mapped that drive in my Database Server and I am trying to link that folder in the SSIS package, but the event is not firing.
Please provide me some solution for this and revert for any clarifications.
I am using this task as the first task in my control flow.I want to check if a particular file exists in the particular location.IF exisists run the package else no.
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
I know a WMI event watcher can be used to watch for a new file being added to a folder. However, I need to check for new folders being added to an existing folder. I haven't been able to find a post on doing this. Is there a way in WQL to check for a new folder being added instead of a new file? I've used SQL for years, but am new to SSIS.
datasource jdbc in UAT Server has been modified to use ms sql driver & point to the UAT sql server . I Really not understand what is exactly meaning of modified and pointing (but i know theory any application need to get access that should point to server )or otherwords we say application is pointig to database, to point one server to other what we do get that . could any one give detail idea really i am missing some basics drivers and application pointing . may i know any book or link to study more about this kind of sutuation
I am new to SQL and I am trying to have a column in my table that points to images I have stored in a directory on my system. I cannot seem to get it to work correctly and I am wondering if someone might be able to help. I want to use this image for each record with the other information on my windows form but I cannot seem to get the file pointer right. Any help would be appreciated. Thanks.
now, when in insert a row in "TV" the foreign key "IdCamera" will relate to a row in either "InfraredCamera" or in "DaylightCamera" depending on the "DeviceType" value.
in other words, if i insert a row with DeviceType=0, then IdCamera will have to point to a row in the "InfraredCamera" table. And if i insert a row with DeviceType=1, then IdCamera will have to point to a row in the "DaylightCamera" table.
so, my question is, how can i make the constraints relationship so that the idCamera relates to a row in DaylightCamera or in InfraredCamera depending on the value of DeviceType? should i make 2 foreign keys with allow null? or should i place both relationships to the same foreign key? im not sure what to do
Thanks guys for your help. it is really appreciated!
How do I "point" a set of stored procedures to operate on different linked servers?
In other words, I have the following linked servers:
DatabaseA DatabaseB DatabaseC DatabaseD ...and in the future, there may be added additional linked servers.
All the linked servers have identical schema, but they contain unique data--each linked server represents a company.
I have a database which will contain stored procedures which I will want to operate against these linked servers. How can I "redirect" my stored procedures to operate against a chosen linked server?
If these were not linked servers, but SQL Server databases, I'd be able to replicate the same stored procedures in each database. Then, when I called a stored procedure, it would act against the data in that database. But these aren't SQL Server databases, so that idea is out.
Unfortunately, the USE command cannot be used within a stored procedure, and even if you could, I can't get it to respond to a database name given as a variable. That idea is out.
The only alternative I have left is to use the string catenation facility of the EXECUTE command. Unfortunately, with 100s of complex queries, setting that up is going to be a nightmare.
I have a .ADP application (access 2000 front end, SQL Server 2000 back end) I basically want to create a new .ADP file and point it to a different SQL Server 2000 Database.
I've copied the current SQL Server DB and recreated it. Same for the .ADP file, but it is currently pointing to the old Db, where do I go to have it point to the new SQL Server DB?
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM Log SQL Server (Current - 10/25/2011 10:55:00 AM) Source spid Message Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I've started working at an organization that has a sql 2005 cluster with a named instances on it, lets call it Instance1. What they done is to create a dns alias for servername that is the same as the instance name, so when you connect to the sql server you connect "Instance1Instance1".
We want to move to SQL 2014 cluster with AlwaysOn Availability Groups doing the HA/DR, the question is if I create a listener name called "Listener1" is there a way to using DNS or anything else to point "Instance1Instance1" to "Listener1"
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup) We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers. We were having
Event Type: Failure Audit Event Source: Security Event Category: Object AccessEvent ID: 560 Date: 5/23/2007 Time: 6:27:15 AM User: domainuser Computer: MACHINENAME Description: Object Open: Object Server: SC Manager Object Type: SC_MANAGER OBJECT Object Name: ServicesActive Handle ID: - Operation ID: {0,1623975729} Process ID: 840 Image File Name: C:WINDOWSsystem32services.exe Primary User Name: MACHINE$ Primary Domain: Domain Primary Logon ID: (0x0,0x3E7) Client User Name: User Client Domain: Domain Client Logon ID: (0x0,0x6097C608) Accesses: READ_CONTROL Connect to service controller Enumerate services Query service database lock state
In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?
How to "CREATE credentials pointing to SQL SERVER logins" and "creating proxys for SQL server agent pointing to SQL SERVER login.
As The SQL server login does not display in the CHECK NAMES list when we are trying to select the identity but All the other Windows logins display correctly in this list .
So using the SQLserver login fails to run a SQL server Job (which uses the credentials and proxies pionting to SQL server logins)
I am not sure if this is necessarily a simple question, but I'm somewhat new to SQL so I thought maybe there's an obvious answer I just don't know about.
The problem is that I have one "master" table, and a child table that has two foreign key references back to that master table. Both of these foreign key constraints are marked as "on delete cascade" with the intention that should a row from the master table be deleted, any rows that reference that object in EITHER foreign key field should be deleted.
I am wondering why this causes a cycle. It seems logical enough to me, it just involves two passes of the table, one for each affected column.
Installed File watcher task, then created a new SSIS package by adding the file watcher task in the control flow, below are my setting's inside the file watcher task editor: 2. Options Filter - *.* Find Existing Files - True Timeout warning - False 3. Path Path input type: Direct imput Path - \server1Sourcefolder Include subdirectories - False Actually my understanding about this task is: This file watcher task will constantly check for the file and then will execute the SSIS package. If my understanding is correct, then where do we specify the name of the SSIS package?? And also do we need this SSIS package which has the File watcher task to run every 5 minutes or so, so that it can keep checking for the file existence. Please help, thanks!!
In the file watcher task in the filter section does it look for exsiting file name which we have mentioned or is it possible to give first few letters of the file name like pat*.txt
I have to make a application using filesystemwatcher which watches the file on the remote server. If that file is changed then I have to fetch that file from ftp location to the server. I have made the SSIS job to fetch the file but not able to set a file system watcher on remote server that watches the file. If the file is changed then I need to insert a row in the SQL server database and then fetch the file.
I'm trying to use a WMI Event Watcher Task as a directory watcher. I know it is available from Konesans at http://www.sqlis.com/23.aspx but they don't feel obliged to make this open source yet. I definitely appreciate the contribution of the binary to the community.
I'm very interested in understanding how the event handler gets the path of the file that caused the event. I added a Script Task to the Event Handler tab and it fires as expected when I create a new file in the directory. I can retrieve the value of the SourceName which is just the name of my Event Watcher Task. I can't find documentation on an object model that is exposed through SSIS that would reveal the name or path of the file, although it's clear this information is available in direct WMI queries from code or a WSH script.
My only guess is that you need to do a workaround in Script (or a custom module) by querying the directory for the most recent file (or list of files compared to a list of processed files) rather than a synchronous feed of the path directly into the SSIS event handler. Ideally, you'd like to get one event per new file created, although this might not be deterministic with the way the OS fires these events and SSIS responds to them. So at the end of the day, querying the directory for recently created files may be the only reliable way of getting the (list of) new files.
I know that this can take hours and hours to figure out since you really need to understand how WMI interacts with SSIS but I would be indebted if you can shed some light on this for me and the community.
One of our developers has written a view which will execute completely (returns ~38,000 rows) in approx 1 min out of SQLMS (results start at 20 sec and completes by 1:10 consistently).
However, if he adds a data flow task in SSIS, adds an OLEDB Data Source and selects Data Access Mode to "Table or view" and then selects the same view, it is consistently taking over 30 minutes (at which point we've been killing it). I can see the activity in the Activity Monitor, it is doing a SELECT * from that view and is runnable the whole time.
If we modify the view to SELECT TOP 10, it returns in a short time.
Has anyone run into this problem? Any suggestions? It is very problematic, as if the views change we have to hack around this problem.
I would like this task to watch for a particular text file in the particular folder.
here
Connection managers:
servename=\localhost
namespace= ootcimv2
WMIQuerySourceTypeirectinput
WMIQuerySource:
SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE Targetinstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name="c:\\ImportData\\ est.txt""
Rest of the fields i leave as they are..
I get this error
[WMI Event Watcher Task] Error: Watching for the Wql query caused the following system exception: "Unparsable query.". Check the query for errors or WMI connection for access rights/permissions.
Just curious if there are any tutorials/help files/forums on using this and other Konesans control Flow and data flow task? most import the flow watcher task.