none
WMI data reader task with C#

    Question

  • Hi Guys,

    Basically I am a dba, I am completely new to SSIS...and know basics about c#, I've got a requirement to collect information about sql services on mulitple database servers and other information.

    I wrote the code in C# using WMI code generator and it is in a .txt file.

    Now I m using WMI data reader task in ssis, to loop through multiple servers to collect the information using foreach. Here I got a problem that i need to input the server name in each ADO enumerator to C# code..

    could you please help me how to pass the server name to my code..

    • Moved by Bob BeaucheminMVP Monday, December 02, 2013 11:59 PM Moved to a more relevent forum
    Monday, December 02, 2013 10:23 AM

Answers

All replies

  • Hi,

    Please check below link. it will help you.

    http://agilebi.com/jwelch/2007/09/05/using-the-wmi-data-reader-in-ssis/


    indu

    Monday, December 02, 2013 10:44 AM
  • Hi Indu,

    Thanks for your reply, but my requirement is I got my custom code in C# to fetch the results.. I cant use direct input in WMI data reader task..but all i need to supply to the code is the server name..

    Kiran.

    Monday, December 02, 2013 11:45 AM
  • You can use a Script component source containing your C# code in a Data Flow Task.  Add the desired output columns in the Script component source designer.  In the script, override the PreExecute, PostExecute and CreateNewOutputRows methods with your WMI reader implementation.  See http://technet.microsoft.com/en-us/library/ms136060.aspx for an script source example.

    To pass the server name, assign the server name to a Foreach Loop container SSIS variable with your Data Flow task inside.  The script can then retrieve the server name from the variable.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, December 02, 2013 12:26 PM
  • Hi Kir4n,

    You can also place a Script Task in the Foreach Loop Container. To make the Script Task to access the variable mapped in Foreach Loop Container, we can add this variables to ReadOnlyVariables or ReadWriteVariables lists in the Script Task Editor. Here is the code example:

    string fileName = (string) Dts.Variables["FileName"].Value;

    References:

    Regards,


    Mike Yin
    TechNet Community Support

    Tuesday, December 03, 2013 3:51 PM
  • Thank you all, especially Dan and Yin, I am trying your valuable suggestion...... :)

    Tuesday, December 17, 2013 7:30 PM
  • Hi Dan...

    when i m using the script task or script source component task.. I m unable to use the system.Management namespace...

    I need that namespace to make dynamic connections from the task and to fetch the results.

    can  you pls help me

    public void Main()
    {
    // TODO: Add your code here
    
    try
    {
    
    ConnectionOptions connection = new ConnectionOptions(); // Building a connection obj -- can i do this in script task
    
    string SQLVirtualName = (string)Dts.Variables["Servers"].Value; //Saving server name from SSIS variable.
    
    ManagementScope scope =
    new ManagementScope("\\\\" + SQLVirtualName + "\\root\\MSCluster", connection);
    scope.Connect();
    
    //Connecting to the target cluster namespace
    ObjectQuery query2 =new ObjectQuery(
    "Select * from MSCluster_ResourceGroupToResource where GroupComponent= 'MSCluster_ResourceGroup.Name=\"" + SQLVirtualName + "\"'");
    ManagementObjectSearcher searchSQL =
    
    new ManagementObjectSearcher(scope, query2); //Querying the WMI using the conn we built before
    
    //Filtering the result//
    List<string> SQLServer = new List<string>();
    foreach (ManagementObject queryObj in searchSQL.Get())
    {
    string var = queryObj["PartComponent"].ToString();
    var = var.Substring(25);
    
    var = var.Replace(
    
    "\"", "");
    //SQLD.Add(var);
    if (var.StartsWith("SQL Server") || var.StartsWith("SQL Server ("))
    {
    
    if (var.StartsWith("SQL Server Agent") || var.StartsWith("SQL Server Full") ||
    var.StartsWith(
    
    "SQL Server Reporting") || var.StartsWith("SQL Server Analysis") ||
    var.StartsWith(
    
    "SQL Server Active") || var.StartsWith("SQL Server Browser")
    || var.StartsWith(
    
    "SQL Server Integration") || var.StartsWith("SQL Server V"))
    continue;
    else
    SQLServer.Add(var);
    
    }
    
    }
    
    Console.WriteLine(SQLServer.Count); //help me here, instead of writing to a command prompt can i store in a variable or flat file using script
    }
    
    catch (ManagementException e)
    {
    MessageBox.Show("An error occurred while querying for WMI data: " + e.Message);
    }
    
    //MessageBox.Show(Dts.Variables["Servers"].Value.ToString());
    Dts.TaskResult = (
    
    int)ScriptResults.Success;
    }


    • Edited by Kir4n Wednesday, December 18, 2013 1:37 PM adding sample code
    Wednesday, December 18, 2013 1:21 PM
  • when i m using the script task or script source component task.. I m unable to use the system.Management namespace... 

    Just like a stand-alone C# application, you need to add a project reference to assembly reference System.Management.Instumentation (and perhaps System.Management) in the script editor.  This assembly contains the needed WMI objects.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, December 18, 2013 1:39 PM
  • Thank you dan for the quick reply :), I've added the reference.. srry to trouble you..

    Just few last questions

    I've embed a sample code of my program, most of them i extracted and modified via WMI code creator...

    so.. can you pls guide me to the comments there.

    also.. can I pass two variables to the foreach loop container at the same time..

    advance thanks dan.. it is really helping me a lot....

    Wednesday, December 18, 2013 1:49 PM
  • also.. can I pass two variables to the foreach loop container at the same time..

    Yes, you can specify multiple variables in the foreach ADO Enumerator.  Under variable mappings, specify the SSIS variable name and zero-based index for each column in the ADO recordset you want to map.

    In the Script properties, specify the variables in the ReadOnlyVariables property.  This will allow you to access the SSIS variables in the C# script code:

    string serverName = Dts.Variables["ServerName"].Value.ToString();
    string instanceName = Dts.Variables["InstanceName"].Value.ToString();
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Kir4n Sunday, December 22, 2013 6:15 PM
    Thursday, December 19, 2013 2:54 AM