SQL Server Bi step by step SSIS 6-obtain the available disk space of the remote host

SSIS is not only an ETL tool, but also has powerful functions. Taking its WMI data reader task and event watcher task as examples, you can even obtain a lot of information about the operating system. Windows Management Instrumentation is one of the "top secrets" in windows. WMI can use WQL to query the following applications:
1. read System Event Logs to find a specific error
2. query the list of running applications Programs
3. query the amount of memory available debugging during package execution
4. determine the free disk space on the disk
Of course, you can also use C # To Call WMI for these applications. However, with SSIs, you can combine these applications with database operations, data conversion, and integration to play a more powerful role.
requirement:
you need to obtain the available disk space of the remote server.

Implementation:
1. First, check the disk size of the remote computer. You must have the Administrator permission for this host or be able to run the package as an administrator.
2. Create an SSIS package named wmi_query, add a WMI data reader task to the control flow, and double-click to set the period attribute:

You can enter the host name, user name, and password for the connection directly created by wmiconnection. Of course, you can also use the dynamic connection method here, so you need to directly change the connection string of wmiconnection.

 

The WQL input method is direct input. You can also enter the variable name file here. The statement for querying disk space is:

  select   freespace  ,   DeviceID  ,   size  ,   systemname  ,   description from  

Win32_logicaldisk
 

 
WhereDrivetype=3
 

In WMI, drivetype indicates the drive type. 0 indicates unknown, 1 indicates movable, and 2 indicates fixed,3Represents the network, 4 represents the CD-ROM, 5 represents the RAM disk, we set the destination as a variable, and directly create a new string variable diskusageresults.
3. For ease of viewing, we can add a script task to output this variable. A pop-up window will pop up to display the disk information:

 
Public voidMain ()
 

 
{
 

// Todo: add your code here
 

 
MessageBox. Show (DTS. Variables ["Diskusageresults"]. Value. tostring ());
 

DTS. taskresult = (Int)Scriptresults. Success;
 

 
}
 

Run the package. We can see the usage of all disks on the remote machine.

Conclusion:
1. This kind of feature may be useful in less cases, but SSIS provides a wide range of features and can become a choice among solutions to many problems.
Resource:
1. WMI data reader task query http://www.sqlservercentral.com/articles/Integration+Services+ (SSIS)/67428/
2. Graphic SSIS monitoring folder and automatic import data http://blog.csdn.net/jinjazz/archive/2008/07/29/2730135.aspx

Author: lone knight (like a year of water)
Source: http://lonely7345.cnblogs.com/
The copyright of this article is shared by the author and the blog. You are welcome to repost this article, but you must keep this statement without the author's consent andArticleThe original text connection is clearly displayed on the page. Otherwise, the legal liability is retained.