10. Add an OLE DB Command Transformation
The last step is to add an OLE DB Command transformation that will insert the WMI data in the dbo.SQL_Drives table. Drag the OLE DB Command transformation icon from the Toolbox's Data Flow Transformations section to the design surface of the Data Flow tab. Right-click the OLE DB Command and select Edit to open the OLE DB Command Transformation Editor. On the Connection Managers page, enter DBACoolStuff in the OleDBConnection field. In the SQL Command field on the Component Properties page, enter the command: INSERT INTO dbo.SQL_Drives (Drive_Letter, [Server], MBFree, TotalSize, DayRun) values (?,?,?,?,?). On the Column Mapping page, configure the fields as follows:
Input Column: Destination Column
DriveLetter: Param_0
SrvName: Param_1
FreeSpaceMB: Param_2
TotalSizeMB: Param_3
DayRun: Param_4
Save the WMICollectDrivesInfo package by choosing Save Selected Items on the File menu. Figure 5 shows what the Data Flow component should look like.
Figure 5: The Data Flow task in WMICollectDrivesInfo |
 |
The entire package should look like the one in Figure 6.
Figure 6: The entire WMICollectDrivesInfo package |
 |
The Results
You need to run the WMICollectDrivesInfo package under an account that has permission to create files. Let's say the dbo.ServerList_SSIS table contains the server list shown in Table 1.
Table 1: Sample Server List in dbo.ServerList_SSIS |
 |
If these were real servers in a network, you might get data similar to that in Table 2.
Table 2: Sample Results in dbo.SQL_Drives (click to enlarge) |
 |
As I mentioned previously, you can easily adapt the WMICollectDrivesInfo package to collect more than just logical drive space statistics. MSDN has a wealth of information about the types of data you can collect and how to do so. MSDN's coverage of WMI starts at msdn.microsoft.com/en-us/library/aa394582.aspx. For information about writing WQL queries, go to MSDN's "WQL (SQL for WMI)" web page.