dcsimg
Results 1 to 4 of 4

Thread: Sql => Ssis => Wmi

  1. #1
    Join Date
    Nov 2008
    Posts
    2

    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)

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You may take look at service broker in sql2k5.

  3. #3
    Join Date
    Nov 2008
    Posts
    4
    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.

  4. #4
    Join Date
    Nov 2008
    Posts
    2
    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
  •