Problem exectuing WMI query on remote server using SSIS

  • I am trying to execute the wmi query (select * from Win32_GroupUser)

    on remote servers and store the result. I am using SSIS to do this.

    I am using script task in ssis to connect to remote server and and get the results and store in the sql server by opening a connection in the script Task. the script task executes for 5 mins inserts some records and I receive the following error

    Unclosed quotation mark after the character string '',')'. Incorrect syntax near 'o'.

    I also tried using WMI Data reader Task. It gives the following errror

    Large WMI notification query may cause a quota violation

    Please advise.

    Thanks!

  • I am using the following code for it

    Imports System

    Imports System.Data

    Imports System.Data.SqlClient

    Imports System.Data.SqlTypes

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports System.Management

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Public Sub Main()

    Dim myConnectionOptions As New System.Management.ConnectionOptions

    Dim text As String

    Dim grpComp As String

    Dim grpName As String

    Dim partGroup As String

    Dim txt2 As String

    Dim domain As String

    With myConnectionOptions

    .Impersonation = System.Management.ImpersonationLevel.Impersonate

    '* Use next line for Win prior XP

    .Authentication = System.Management.AuthenticationLevel.Connect

    End With

    Dim myManagementScope As System.Management.ManagementScope

    Dim myServerName As String

    myServerName = "WIN-QFBQBMPC5KJ"

    myManagementScope = New System.Management.ManagementScope("\\" & myServerName & "\root\cimv2", myConnectionOptions)

    '* connect to WMI namespace

    Try

    myManagementScope.Connect()

    'MsgBox(myServerName)

    Catch ex As Exception

    MsgBox("Could not Connect to this server")

    MsgBox(myServerName)

    End Try

    Dim cm As Microsoft.SqlServer.Dts.Runtime.ConnectionManager

    Dim sqlConn As OleDb.OleDbConnection

    Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters90

    cm = Dts.Connections("sqlConn2")

    cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters90)

    sqlConn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)

    If sqlConn.State = ConnectionState.Open Then

    sqlConn.Close()

    End If

    Dim command As New OleDb.OleDbCommand()

    With command

    .Connection = sqlConn

    .Connection.Open()

    End With

    Dim searcher As New Management.ManagementObjectSearcher("root\cimv2", "SELECT * FROM Win32_GroupUser")

    For Each queryObj As Management.ManagementObject In searcher.Get()

    text = CStr(queryObj("GroupComponent"))

    txt2 = CStr(queryObj("PartComponent"))

    Dim cmdText As String = "INSERT INTO GroupUser_Name(groupName,PartName,Servername) VALUES('" & text & "','" & txt2 & "','" & myServerName & "')"

    Dim command1 As New OleDb.OleDbCommand(cmdText, sqlConn)

    command1.Connection = sqlConn

    With command1

    .CommandText = cmdText

    .Connection = sqlConn

    .CommandType = CommandType.Text

    .ExecuteNonQuery()

    End With

    Next

    sqlConn.Close()

    sqlConn.Dispose()

    Dts.TaskResult = Dts.Results.Success

    End Sub

    End Class

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply