-
Sql => Ssis => Wmi
I have a SQL 2005 database.
When a new record in a special table is inserted, then I want to SELECT some data and transfer the data via WMI to another program.
I did succeed to enter data to the program by manually executing a .vbs script (see quote below).
My idea is to use a TRIGGER in the SQL-table to start a SSIS-package that do the SELECT and transfer to WMI.
I have found in VisualStudio two WMI-controls: "WMI Data Reader Task" and "WMI Event Watcher Task" - but from the titles it do not seems as they also can "deliver" data for input by WMI to an external program?
I am new to WMI - and not expert (yet ;-) in SSIS and TRIGERS.
I will be very glad for any comments on if I am on the right path, or some other ways is more easy, stable etc.
And of course if you have any tips, tricks, links to "copy & paste" exemples etc :-)
The vbs i looking something like this:
Set wbemServices= GetObject("winmgmts:\\" & strComputer & "\root\customProgram")
Set oReg = wbemServices.Get("Lnl_IncomingEvent")
Set oMethod = oReg.Methods_.Item("SendIncomingEvent")
Set oInParam = oMethod.InParameters.SpawnInstance_()
oInParam.Source = "Location 1"
oInParam.Device= "GPS Device 1"
oInParam.Description = "Test Event From WMI"
call wbemServices.ExecMethod("Lnl_IncomingEvent", "SendIncomingEvent",oInParam)
-
You may take look at service broker in sql2k5.
-
It indeed does not sound wise to me to execute SSIS packages as a response to a data-update, it would be way to heavy.
You might consider writing a custom .NET CLR and triggering that.
Last edited by KobeLenjou; 11-25-2008 at 10:08 AM.
-
Thanx, both of you, I did not get answers by email so I found them ...later... ;-|
For sure SSIS is to heavy, and using the TRIGGER is dangerous as problems in the TRIGGER-initated proces can result in hanging Inserts or even Failed Inserts.
SOLUTION I did is to use the TRIGGER => to call a .BAT => that calls a .LNK (windows shortcut) => that calls the Executeable (.EXE).
Calling either only a .BAT or a .LNK keeps control at the TRIGGER (waiting/hanging for resultcode), but I found that the solution with BAT-LNK-EXE returns control back to TRIGGER between the BAT and LNK,
so even if the EXE-file is not found at all, or is hanging - the SQL (Insert) just continues immediately :-)
I'm looking forward to investigate your hints, ...later... ...sune...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|