October 17, 2008 at 3:42 am
I have been playing around with the WMI data reader task after being asked if SSIS could be used to loop through several servers and extract event log information.
The problem I have encountered though is that the resultset returned by the task appears to be comma delimited and some of the fields already contain commas so you end up with values everywhere when trying to use the dataset. As far as im aware there doesn't seem to be a way to append pipes to the columns in the wql query or manipulate them from there.
I have also tried reading the data into a variable but then when reading the data from the variable into other variable so i can work with the data a similar issue seems to occur with messages returned saying the data type of the variable is static and the value that is being attempted to pass to the variable is incompatible, I have tried setting all the destination variables im trying to populate to strings but the same error ocurrs.
I have extracted event log data before using the log parser tool from microsoft but I was hoping the wmi data reader task would prevent the need to use this tool.
Has anyone else tried event log extracts in SSIS and encountered the same problem?
Any pointers would be appreciated.
Thanks
March 15, 2010 at 1:05 pm
Hi there,
Did you ever get this up and running? I'm currently running a process using windows batch files, scheduled tasks, VBscript and bulk import & its all over the place.
I'm keen on getting something more solid, something like your solution in place.
cheers, Mark
March 16, 2010 at 2:43 am
I never got the WMI reader to work with event log extracts in the way I had hoped so i had to abandon using SSIS to extract event log data. I did use the WMI data reader to extract some system info such as available and total disk space, memory configuration just to prove it could be useful in some instances but never took it any further than that.
March 16, 2010 at 11:25 am
You know there are a couple ways to do this.. I use a WMI Reader task to populate an object variable and then use a Script-Task as a Data source in a Dataflow with this code to read it:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub CreateNewOutputRows()
Dim dataTable As System.Data.DataTable
Dim dataRow As System.Data.DataRow
dataTable = CType(Me.Variables.DiskUsageResults, Data.DataTable)
For Each dataRow In dataTable.Rows
With DiskFreeSpaceBuffer
.AddRow()
.Description = dataRow.Item("Description").ToString
.DeviceID = dataRow.Item("DeviceID").ToString
.FreeSpace = dataRow.Item("FreeSpace").ToString
.Size = dataRow.Item("Size").ToString
.SystemName = dataRow.Item("SystemName").ToString
.Filesystem = dataRow.Item("Filesystem").ToString
End With
Next
DiskFreeSpaceBuffer.SetEndOfRowset()
End Sub
End Class
DiskUsageResults is a read-only variable for the script.
The WMI reader is setup in the attached WMITest.jpg image.
And the query which is cutoff in the image is:
SELECT FreeSpace, DeviceId, Size, SystemName, Description, FileSystem FROM win32_LogicalDisk WHERE DriveType= 3
Just to show this can work..
CEWII
March 17, 2010 at 2:35 am
I do something similar fo getting disk info and other system info, have you set anything up that reads data from the event logs as that is when i found things started to get tricky.
March 17, 2010 at 1:58 pm
I haven't done that but what does your select statement look like in the WMI reader?
CEWII
March 18, 2010 at 2:56 am
I was basically doing a full extract so the following: Select Category, CategoryString, ComputerName, Data, EventCode, EventIdentifier, EventType, Logfile, Message, RecordNumber, SourceName, TimeGenerated, User From Win32_NTLogEvent
WHERE Logfile <> "Security".
It comes out as comma delimited but as there are commas within some of the fields like the message field for instance it needs text qualifiers or a different delimiter like the pipe character. But there didn't seem to be anyway of doing that within the wql query and i couldn't find a way around it. Admittedly it was a long time ago now and I have not tried this with SSIS 2008 so i don't know if there are any new options available for doing this. Its not something I have to do anymore but as with most DBAs it really bugs me if i cant figure out a solution so its out of interest now rather than necessity, but will still appreciate any suggestions or info that may be helpful.
March 18, 2010 at 10:01 am
The only way I could get event log data into SQL was to run an independent VB script, and output to a CSV file, wrapping each element in " characters to avoid the mid-text comma characters. I then read the CSV files in.
Its a clumsy solution, and it would be really elegant to be able do this entirely in SQL server and not rely on external VB scripts.
If somebody can do this in either t-sql or SSIS, it would be awesome. An ADO.NET or OLEDB driver for WMI would be equally cool.
March 24, 2010 at 9:07 am
What WMI class are you using? I finally got a chance to really dig into this..
CEWII
March 24, 2010 at 2:50 pm
I have attached something I threw together that shows two different methods, the first takes the output from the WMI Data reader in a system.object and walks though it. That one does not return InsertionStrings or Data. The other method reads the WMI data a little more directly and it does not return InsertionStrings because I didn't have time to handle it. Take a look-see.
The zip file has the package in it.
CEWII
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply