In SQL Server Integration Services (SSIS) packages, you can use WMI Data Reader tasks to obtain Windows Management Instrumentation (WMI) data from computers. In "Use SSIS to Access WMI Data on Multiple Servers," this data is stored in a file. Alternatively, you can store it in a variable.
The 102564.zip file includes an SSIS package—WMICollectDrivesInfo_Var.dtsx—that uses a variable named WMIOut to store the WMI Data Reader task's output. To obtain the 102564.zip file, click the 102564.zip hotlink at the top of the page.
To create the WMICollectDrivesInfo_Var package, you need to do things differently in three steps:
-
In step 2, you need to define an additional variable named WMIOut, which will store the WMI Data Reader task's output. You also need to specify a different scope for all three variables. Figure A shows the information you need to enter for each variable.
Figure 1: Entering the three variables - In step 4, you only have to create the WMI Connection Manager and OLE DB Connection Manager. The File Connection Manager and the Flat Connection Manager aren't needed.
- In step 8, you need to use a Script component rather than a Flat File Source inside the Data Flow task.
To create a Script component, drag the Script component icon from the Toolbox's Data Flow Transformation section to the Data Flow design surface. When asked, select Source for the Script Component Type.
To configure the Script component, right-click it and choose Edit to open the Script Transformation Editor. On the Inputs and Outputs page, expand Output0 by clicking the + sign. Select Output Columns. Click the Add column button and add the following columns:
- Name: Data Type= string \[DT_STR\]
- SystemName: Data Type= string \[DT_STR\]
- FreeSpace: Data Type= string \[DT_STR\]
- Size: Data Type= string \[DT_STR\]
On the Script page, enter User::WMIOut in the ReadOnlyVariables field. Click the Design Script button and insert the SSIS_User_Script_Component script, which you'll find in the 102564.zip file.
For additional information on how Script components work in SSIS packages, see the web-exclusive article "Scripting Data Flow in SQL Server Integration Services."
