April 5, 2011 at 11:37 am
Hi !
I am using the WMI Query to get the server Info and result will be in dataset variable(Object) . and i would like to Insert the result set variable data to MSSQL destination server table. Can some one guide me inserting the data from result set to destination ?? Thanks in advance
April 5, 2011 at 12:31 pm
Steve how good are you with programming?
I use this function as part of a larger ETL program i wrote; once I have a datatable, i can pass it to this function and it creates a table in the database with the same name.
essentially i makes a CREATE TABLE... script, does an ExecuteNonQuery with it, and then uses SQLBulkCopy to load the data (zillions of times faster than row by row RBAR)
'Requires Imports System.Data and Imports System.Data.SqlClient
Private Sub Datatable_To_SQLTable(ByVal dt As DataTable)
Static rng As New Random
Dim seed As Integer = 3
Dim maxsize As Integer
'build the potential table
Dim TableDef As String
Dim ColDef As String
If dt.TableName = String.Empty Then dt.TableName = "Table_" & ("0000" & rng.Next(0, 9999).ToString).ToString.Right(4)
TableDef = "CREATE TABLE [" & dt.TableName & "](" & vbCrLf
For Each dr As DataRow In dt.Rows
For Each col As DataColumn In dt.Columns
If GetInteger(col.Caption) < GetString(dr(col)).Length + 2 Then
col.Caption = GetString(dr(col)).Length + 2
End If
Next
Next
For Each col As DataColumn In dt.Columns
ColDef = String.Empty
Select Case col.DataType.ToString
Case "System.Decimal", "System.Double"
ColDef = "[" & col.ColumnName & "] decimal(19,4) "
Case "System.DateTime"
ColDef = "[" & col.ColumnName & "] datetime "
Case "System.Boolean"
ColDef = "[" & col.ColumnName & "] bit "
Case "System.Int32", "System.Byte"
ColDef = "[" & col.ColumnName & "] integer "
Case Else '"System.String"
If GetInteger(col.Caption) > 1000 Then
ColDef = "[" & col.ColumnName & "] varchar(max) "
ElseIf GetInteger(col.Caption) = 0 Then
ColDef = "[" & col.ColumnName & "] varchar(30) "
Else
ColDef = "[" & col.ColumnName & "] varchar(" & GetInteger(col.Caption) & ") "
End If
End Select
'AutoIncrement
If col.AutoIncrement = True Then
ColDef = ColDef & "IDENTITY(" & col.AutoIncrementSeed & "," & col.AutoIncrementStep & ") "
End If
'null/not null
If col.AllowDBNull Then
ColDef = ColDef & " NULL"
Else
ColDef = ColDef & " NOT NULL"
End If
'unique
If col.Unique Then
ColDef = ColDef & " UNIQUE "
End If
'default value
If Not (col.DefaultValue Is System.DBNull.Value) Then
ColDef = ColDef & " DEFAULT " & col.DefaultValue.ToString & " "
End If
ColDef = ColDef & "," & vbCrLf
TableDef = TableDef & ColDef
'dt.Columns(i).ColumnName
Next
'any additional columns, including PK, timestamps, must be at the end of the table. first X cols in dt must match first x cols in SQL table.
TableDef = TableDef & "[CreatedDate] datetime default getdate()" & vbCrLf
TableDef = TableDef & ")" & vbCrLf
'create the table
SqlHelper.ErrorMessages.Clear()
SqlHelper.ExecuteNonQuery(Me.ConnectionString, CommandType.Text, TableDef)
If SqlHelper.ErrorMessages.Count > 0 Then
For Each m As String In SqlHelper.ErrorMessages
Debug.Print(m)
Next
Else
'no errors
'now use SQLBulk Copy to get the data into the server, instead of RBAR:
'note my connection string is based on String.Repalce of this:
'Private Const SqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Application Name=MyApplicationName.exe;"
'Private Const SqlTrustedConnectionFormat As String = "data source={0};initial catalog={1};Trusted_Connection=True;Application Name=MyApplicationName.exe;"
Dim myConn As New SqlConnection(Me.ConnectionString)
myConn.Open()
Using myBulkCopy As New SqlBulkCopy(myConn)
myBulkCopy.DestinationTableName = "[" & dt.TableName & "]"
myBulkCopy.WriteToServer(dt)
End Using
End If
End Sub
Lowell
April 6, 2011 at 12:31 pm
RamSteve, please post the code you have so far so we can better assist.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2011 at 12:37 pm
This the WMI Query i was using
SELECT Caption, CSDVersion, CSName, Version FROM Win32_OperatingSystem
and i was following the document as below and now its working fine
http://qa.sqlservercentral.com/Forums/Attachment2144.aspx
Is there any WMI Query where i can get IPAddress ,OS_Version,Domain for the servers ?
Thanks in advance
April 6, 2011 at 1:02 pm
You can get IP address from a DNS lookup on the server name...if you need the whole set of IPs bound to all network adapters on the server then you'll need to dig into WMI a bit more. Posting to a WMI forum (MSDN has one) may get you farther down the path with that...same with domain and OS version *.
* Re: OS version, if you want to move that into the code that will check things within the SQL instance (after determining which instances are on the server and you are able to connect to them) you can find the OS version with this T-SQL code:
DECLARE @on_loc INT
SET @on_loc = CHARINDEX(' on ', @@version) + 4 ;
SELECT SUBSTRING(@@VERSION, @on_loc, 100)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 14, 2011 at 6:53 am
I just ran across this article and thought it might be of use if you have not arrived at a solution yet. The author is solving a portion of your problem case in his example. PowerShell makes dealing with WMI much easier these days: http://www.mssqltips.com/tip.asp?tip=2358&ctc
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 14, 2011 at 8:27 am
Hi !
Can some one guide me how to implement power shell scripts using the SSIS development ,as i need to run on all my present servers(around 300) to get IP address,OS_Version,Domain name,SQL instances, TCP ports numbers ,databases
April 14, 2011 at 9:14 am
RamSteve (4/14/2011)
Hi !Can some one guide me how to implement power shell scripts using the SSIS development ,as i need to run on all my present servers(around 300) to get IP address,OS_Version,Domain name,SQL instances, TCP ports numbers ,databases
You mean calling a PowerShell script from an SSIS package? Use the "Execute Process Task" in SSIS.
WMI is standard equipment on all Windows servers and can be invoked remotely, i.e. you should only need PowerShell and SSIS installed and configured on one machine. From there you can run the same WMI query against all machines needed on your network provided the user running the job has sufficient privileges.
Here is an article to ramp you up on WMI's remote capabilities: http://msdn.microsoft.com/en-us/library/aa389290(v=vs.85).aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply