November 3, 2009 at 8:43 am
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!
November 3, 2009 at 7:05 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy