WMI Query Data Reader connection string

  • dave-dj

    SSChampion

    Points: 11230

    Hi all,

    i am trying to configure an SSIS package to read a list of servers from a database into a record set,

    then for each record in the record set, I want to use the server connection string to dynamically set a WMI Data Reader connection string, to query things such as disk space on the server and return the results to a database table.

    Do I need to use a custom script task to set the connection string of the WMI data reader to a variable value? I not quite sure how to read the data set to set the connection string?

    Has anyone got any examples they could point me to?

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • dave-dj

    SSChampion

    Points: 11230

    Hi all,

    this is actually simplier than I thought, now i've had a little more time to look at it.

    I can do it by setting a string variable at the pack level (in theory.....)

    I'm still having an issue at present, where I am trying to dynamically set the ServerName of the WMI Connection Manager, by putting an expression on the WMI Connection for ServerName. The expression I'm using is:

    "\\\\" + @[User::ServerConnString] - (I've defined @[User::ServerConnString] with a scope of package)

    Using breakpoints and watch variables, I can see that the string variable @[User::ServerConnString] is being updated in the for each loop correctly, but when the WMI query executes, it only seems to run against the local machine, ignoring the WMI Connection expression for ServerName ???

    I've tried removing the slashes, but this then fails due to 'an invalid parameter'?

    Also, if I manually set @[User::ServerConnString], the correct server is queried.

    Any ideas, where I am going wrong??

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • dave-dj

    SSChampion

    Points: 11230

    I found a BUG!

    I stumbled across this post, trying to find other examples to see if I'd missed something.

    This post verifies the problem (which is dynamically setting the ServerName doesn't work) and the solution (which is to construct the whole connection string).

    For full details, check this post:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=377218

    Case Closed!

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • gogabees

    SSC Veteran

    Points: 243

    Hi there,

    I was trying the same thing to Cycle trough server names with the WMI Connection to launch queries against multiple servers. I have read the link that you posted but it does not make sense or I am interpreting it wrong (as English is not my first language). Could you if possible could you give us a step by step how to Create the right connection?

    I have followed the steps and all I get in the end is RPC server is Unavailable so I take it I have done something really wrong.

    Any help would be much Appreciated.

    Donovan

  • dave-dj

    SSChampion

    Points: 11230

    OK,

    hopefully I won;t miss anything, but I've attached a PDF (WMI QUERY Guide.pdf ) on how to do a WMI Query for server disk usage.

    I think point 8 is probably where you are going wrong.

    Please let me know how you get on and if it's any help.

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • gogabees

    SSC Veteran

    Points: 243

    Dave! Your a Legend!!! Works like a charm. Thanks man.

    Was quite a lot more complicated than the post makes it out to be, and following your steps to the T was spot on.

    Thanks again man

    Donovan

  • dave-dj

    SSChampion

    Points: 11230

    Excellent. I'm Pleased it worked.

    I'm just returning the favour that many others have done me on this forumn.:D

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • Jason Crider

    SSCertifiable

    Points: 6019

    On the Script Component within the last Loop I'm getting:

    Name 'OutputBuffer' is not declared.

    I went through the pdf and I noticed some parts are missing but I can't figure this part out.

    Any ideas?

  • dave-dj

    SSChampion

    Points: 11230

    is it declared as a source?

    also. did you define the output columns?

    another thing - check that your script containers all the imports - if it doesn't then that could case a problem.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • Jason Crider

    SSCertifiable

    Points: 6019

    Disclaimer:I am no programmer. This is a weak area for me so terminoligy may be weak.

    is it declared as a source?

    I have no clue.

    also. did you define the output columns?

    Yes

    another thing - check that your script containers all the imports - if it doesn't then that could case a problem.

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    I used the same that you had in the pdf:

    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()

    '

    ' Add rows by calling AddRow method on member variable called " Buffer"

    ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"

    '

    With OutputBuffer

    .AddRow()

    .Description = Variables.Description

    .DeviceID = Variables.DeviceID

    .FreeSpace = Variables.FreeSpace

    .Size = Variables.Size

    .ServerName = Variables.SystemName

    End With

    End Sub

    End Class

  • dave-dj

    SSChampion

    Points: 11230

    Is it defined as a source --> I'm referring to when you add the script compenant task in step 12.

    I'll see if I can spot anything else. Would a copy of the package be helpful?

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • dave-dj

    SSChampion

    Points: 11230

    Could you check that you have done step 14. - (if I change my package to exclude these variable - I get an output buffer errror

    14. Next, select Script and add following variable to ReadOnlyVariables property:

    Description,DeviceID,FreeSpace,Size,SystemName

    Attached is a copy of the package.

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • Jason Crider

    SSCertifiable

    Points: 6019

    It seems that all of it is correct but I still get the error.

    I'm including a pdf attachment that has some of my screens.

  • dave-dj

    SSChampion

    Points: 11230

    Found the error!

    On Page 2 of your PDF you show the Inputs and Ouputs.

    The output name that you have called 'Output 0' is the problem. If you change this to 'Output' and go into your script and come out again (so it recompiles the script) your package should work.

    To explain:

    in the script, it references 'OutputBuffer', which is the buffer for the output that you specified on the Inputs and Outputs screen. So if you changed the Inputs and Outputs root node for our variables to e.g. DiskUsageVariables.

    then within the script, you would have to reference the variables buffer as 'DiskUsageVariablesBuffer' i.e. your script would resemble this:

    ' 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()

    '

    ' Add rows by calling AddRow method on member variable called " Buffer"

    ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"

    '

    With DiskUsageVariablesBuffer

    .AddRow()

    .Description = Variables.Description

    .DeviceID = Variables.DeviceID

    .FreeSpace = Variables.FreeSpace

    .Size = Variables.Size

    .ServerName = Variables.SystemName

    End With

    End Sub

    End Class

    Hopefully that makes sense.;)

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • Jason Crider

    SSCertifiable

    Points: 6019

    It makes a little sense.

    I made the change to Output like you said and it didn't work. I then changed it to DiskUsageVariablesBuffer like your example and it doesn't work because:

    Error 1 Validation error. Data Flow Task: Script Component [538]: Error 30451: Name 'DiskUsageVariablesBuffer' is not declared. Line 19 Column 14 through 37 Error 30456: 'Description' is not a member of 'ScriptComponent_dac8f37147564a90bcc3dc06c6314fb2.Variables'. Line 21 Column 28 through 48 Error 30456: 'DeviceID' is not a member of 'ScriptComponent_dac8f37147564a90bcc3dc06c6314fb2.Variables'. Line 22 Column 25 through 42 Error 30456: 'FreeSpace' is not a member of 'ScriptComponent_dac8f37147564a90bcc3dc06c6314fb2.Variables'. Line 23 Column 26 through 44 Error 30456: 'Size' is not a member of 'ScriptComponent_dac8f37147564a90bcc3dc06c6314fb2.Variables'. Line 24 Column 21 through 34 Error 30456: 'SystemName' is not a member of 'ScriptComponent_dac8f37147564a90bcc3dc06c6314fb2.Variables'. Line 25 Column 27 through 46 wmi.dtsx 0 0

    So, at what level should I declare DiskUsageVariablesBuffer and what data type?

Viewing 15 posts - 1 through 15 (of 46 total)

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