WMI Data Reader Task in SSIS with example

In This article i will discuss about WMI Data reader task. WMI(Windows management instrumentation) Data reader enables you to interface with server or work station by writing WQL. WQL is a WMI query language and is like T-SQL query language.The output of the query can be saved either in variable or file connection for later use.

Following are some applications for which you could use this task

• Read Event viewer log for any errors or warnings
• Reads size and other details of drive
• List out the application which are currently running on the server
• Getting RAM available space for package execution or debugging
• Gets list of applications and their current version installed

Drag and drop WMI Data Reader Task from tool box onto control flow design as shown in Figure 1

Figure 1

Double click on the task which opens up configuration editor as shown in Figure 2

Figure 2

In General tab you have to specify name and description of the task. Next tab is WMI options tab where you have to specify required properties for the task as shown below Figure 3

   Figure 3

Specify WMIConnection connection by selecting <New Connection…> option under WMIConnection which pops up WMI connection Manager editor as shown below Figure 4

Figure 4.

• Name: provide a name for the connection manager.
• Description: Describe the connection manager.
• Server name:provide the name of the server to which you want to make the WMI connection.
• Namespace:Specify the WMI namespace.
• Use Windows authentication: Select to use Windows Authentication. If you use Windows Authentication, you do not need to provide a user name or password for the connection.
• User name and password should be provided if Windows authentication is not selected.
• Test Options allows you to verify connection.

For the purpose of example, WMI connection is created with stand-alone work station(Local host) server as shown in Figure 4. 

Root\Cimv2 is default namespace for writing WQL queries

Other options in WMI options tab are as follows.

• WqlQuerySourceType option specifies source type of WQL query which can either direct input or file connection or variable.
• WqlQuerySource option specifies source of WQL query. Source will be provided based up on source type specified.
• OutputType: this option specifies whether you want output of query to retrieve just values or also column names with values
• OverWriteDestination: option specifies whether data in destination file or variables is kept,overwritten or appended.
• DestinationType: specifies destination type.
• Destination:Specifies the file connection or variable to save output of the task

For the purpose of example, The following query gets free space,DeviceID, Size of desk,System name about of C Drive.

SELECT FreeSpace, DeviceId, Size, SystemName, Description FROM Win32_LogicalDisk WHERE DeviceID = 'C:'

Once configuration completed WMI options tab should look like below figure 5

Figure 5

Execute the task.The output of the task will be saved in text.txt file as shown below

Description, Local Fixed Disk
DeviceID, C:
FreeSpace, 2424451072
Size, 36709388288
SystemName, INLTE5569

Run wbemtest.exe command which takes you to WMI tester Menu. Where you can write your WQL queries to test.

NOTE: Please leave comment if you the post