Problem exectuing WMI query on remote server using SSIS

• sandeep_007

  SSC Veteran

  Points: 215

  More actions

  November 3, 2009 at 8:43 am

  #206225

  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!

• sandeep_007

  SSC Veteran

  Points: 215

  More actions

  November 3, 2009 at 7:05 pm

  #1074631

  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