Getting Drive Info Part 2, DTS

In this installment of the drive info series we will gather the drive information via a DTS package. Part 1 of this series used the File System Object from the Windows Scripting Host via the sp_OA suite of stored procedures to gather the drive information. The File System Object will be used again to accomplish this task, but this time from an ActiveXScript task. Using this method can only collect drive information from the server where this DTS package is run. The drive information can be stored on any SQL Server.

Open a new DTS package in the DTS Designer and create a new Connection object that points to a SQL Server where the drive information is going to be stored. Set the database to tempdb or the database that will hold the DriveInfo table which will be created in the package.

Create a new string global variable named DriveInfoXml.

The next thing that needs to be done is create the table to hold the data. Drag an Execute SQL task into the DTS Designer and add the following SQL statement.

CREATE TABLE DriveInfo

IF NOT EXISTS(SELECT 1
                FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_NAME = 'DriveInfo')
CREATE TABLE dbo.DriveInfo
( Drive VARCHAR(255)
, DriveSize FLOAT
, FreeSpace FLOAT)

Here is a screen shot of the Execute SQL task set up.

CREATE TABLE DriveInfo

Now we need to collect the drive information. Collection of the drive information is accomplished using the File System Object from an ActiveX Script task. The script accomplishes the same thing as the sp_OA sql script, but looks less like dynamic sql and doesn’t use xp_fixeddrives. The script stores the drive information in XML formatted data to be used in the later steps which insert the data in the table. Drag an ActiveX Script task into the DTS Designer, name it “Get Drive Info” and copy the code below into the code area of the ActiveX Script task. Connect an On Success precedence constraint from the Create DriveInfo Table Execute SQL task to the Get Drive Info ActiveX Script task.

'**********************************************************************
'  Get Drive Size
'
'**********************************************************************

Function Main()

	Dim FSO
	Dim Drives
	Dim Drive
	Dim XML

	' Set up global data.
	Const DriveTypeRemovable = 1
	Const DriveTypeFixed = 2
	Const DriveTypeNetwork = 3
	Const DriveTypeCDROM = 4
	Const DriveTypeRAMDisk = 5
	NewLine = Chr(10)
   
	Set FSO = CreateObject("Scripting.FileSystemObject")

 	Set Drives = FSO.Drives

  	'Create an XML doc to use in SQL
	XML = "<drives>"
	For Each Drive In Drives
		If Drive.DriveType = DriveTypeFixed Then
			XML = XML & "<drive DriveLetter=""" & Drive.DriveLetter & """ TotalSize=""" & Drive.TotalSize & """ FreeSpace=""" & Drive.FreeSpace & """/>"
		End If
	Next
	XML = XML & "</drives>"
	DTSGlobalVariables("DriveInfoXml").Value = XML
	MsgBox(XML)
	Main = DTSTaskExecResult_Success

End Function

Here is a screen shot of the Get Drive Size ActiveX Script Task.

Get Drive Size

We use an ActiveX Script task for the next step that builds the sql command for the Execute SQL task to follow. The XML data created previous step is used in a sql script that utilizes the XML features of SQL Server. There are many ways to get this data into a table in a DTS package, but I choose XML just to show another example that didn’t fall back on Ado. I also think it’s kind of silly to use Ado in DTS when there are already database connections to use. It’s probably a little easier to debug as the output of this task can be copied into a query tool and tested.

Drag another ActiveX Script task into the DTS Designer, name it “Build Put DriveInfo” and copy the code below into the code area of the ActiveX Script task. Connect an On Success precedence constraint from the Get Drive Info ActiveX Script task to the Build Put DriveInfo ActiveX Script task.

'**********************************************************************
'  Build Put DriveInfo
'************************************************************************

Function Main()

Dim SQL
Dim oPkg
Set oPkg = DTSGlobalVariables.Parent

SQL = "DECLARE @hdoc int"  & VbCrLf &_
"DECLARE @doc varchar(8000) -- use varchar(max) on 2005 and higher"  & VbCrLf &_
"SET @doc ='" & DTSGlobalVariables("DriveInfoXml").Value & "'"  & VbCrLf &_
"--Create an internal representation of the XML document."  & VbCrLf &_
"EXECUTE sp_xml_preparedocument @hdoc OUTPUT, @doc"  & VbCrLf &_
"-- Execute a SELECT statement that uses the OPENXML rowset provider."  & VbCrLf &_
"INSERT INTO dbo.DriveInfo"  & VbCrLf &_
"SELECT DriveLetter"  & VbCrLf &_
"     , TotalSize / POWER(1024, 2) AS DriveSize  -- in MB for GB make it POWER(1024, 3)"  & VbCrLf &_
"     , FreeSpace / POWER(1024, 2)"  & VbCrLf &_
"  FROM OPENXML (@hdoc, '/drives/drive',1)"  & VbCrLf &_
"          WITH (DriveLetter varchar(255)"  & VbCrLf &_
"               ,TotalSize float"  & VbCrLf &_
"               ,FreeSpace float)"

oPkg.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask.SQLStatement = SQL
	Main = DTSTaskExecResult_Success
End Function

Here is a screen shot of the Build Put DriveInfo ActiveX Script task.

Put Drive Info

The final step in the DTS package is to add the Put DriveInfo Execute SQL Task. The initial sql command is SELECT GETDATE() just to make the parser happy. The actual sql command to execute was generated by the previous step. Here is a sample of the sql command generated by a test run.

DECLARE @hdoc int
DECLARE @doc varchar(8000) -- use varchar(max) on 2005 and higher
SET @doc ='<drives><drive DriveLetter="C" TotalSize="500000878592" FreeSpace="50790670336"/></drives>'
--Create an internal representation of the XML document.
EXECUTE sp_xml_preparedocument @hdoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
INSERT INTO dbo.DriveInfo
SELECT DriveLetter
     , TotalSize / POWER(1024, 2) AS DriveSize  -- in MB for GB make it POWER(1024, 3)
     , FreeSpace / POWER(1024, 2)
  FROM OPENXML (@hdoc, '/drives/drive',1)
          WITH (DriveLetter varchar(255)
               ,TotalSize float
               ,FreeSpace float)

Here is a screen shot of the completed package.

DriveInfo DTS Package

SSIS-DTS Package Search 2.0.18 released

SSIS-DTS Package Search 2.0.18 has been released. This is a bug fix release that addresses a problem with the search of the parameter and ResultSet bindings in the Execute SQL task.

Living with DTS on SQL Server 2005 and 2008

The DTS support in SQL Server 2005 and 2008 is excellent (almost). Typically the packages and jobs do not need any modification in order to run on SQL Server 2005 or 2008. Unfortunately, I have read far too many news group posts that indicate a complete conversion to SSIS MUST take place during a SQL Server 2000 upgrade. It is not mandatory. I am not saying to avoid SSIS. The existing DTS packages can be reviewed for candidates to convert to SSIS during the upgrade. The old DTS packages could also be converted as time permits after the upgrade.

DTS Packages can be run and edited in SSMS as long as the Backwards Compatibility and DTS Designer components from the Sql Server 2005 Feature Pack have been installed. It is available for SQL Server 2005 SP2 here, SQL Server 2005 SP3 here and SQL Server 2008 SP1 here.

The two features that are missing from SSMS are the ability to create a new package from scratch and the detailed list of DTS packages which are available in Enterprise Manager. The lack of these two features in SSMS requires a copy of Enterprise Manager to be available and the continued use of Windows XP since Sql Server 2000 is not compatible with Vista and later versions of Windows.

In order to address these missing features, I have created a new utility and two custom reports for SSMS.

The utility is Create DTS Package. It will create an empty DTS package in a structure storage file or SQL Server.

Create DTS Package

Create DTS Package creates a blank DTS package and saves it in as a structured storage file or in a SQL Server.

Enter the name of the new package in the Package name text box.

If the package storage destination is a structured storage file, enter the full path in the File Name text box and click on the Save To File button.

If the package storage destination is a sql server, choose the authentication, enter the server name in the Server text box, provide the credentials and click on the Save To Sql Server button.

Create DTS Package is an HTA application.

Create DTS Package has eliminated the need for the Enterprise Manager installation in a SQL Server 2005/2008 environment that still needs to support DTS packages.

Create DTS Package will run on both 32(x86) and 64(x64) bit installations.

Create DTS Package could also be modified to add a standard group of objects for your organization to create a package template.

The source code for Create DTS Package is available for download here (right click the link and click Save Target As…\Save Link As..) or here for a listing of the source code (trying to save an hta is causing security violations for some people).

The Package Summary custom report recreates the Local Packages view from Enterprise Manager in SSMS.

In order to support mixed DTS/SSIS environments, the report shows both types of packages.

Since this custom report does not require any object inputs, it can be run from anywhere in the SSMS Object Explorer.

The SQL Server 2005 version is available here.

The SQL Server 2008 version is available here.

Here is a sample output of the report.

Package Summary

Update: If you are running 32-bit Windows 7, there is quite a bit more to do to get the environment set up correctly. See Jason Brimhall’s excellent post SQL 2008 DTS

Update: In order to get DTS working on 32-bit Windows XP with SQL Server 2008 R2, the above steps will need to be followed as well.

Which version of SSIS-DTS Package Search is right for you

The download page for SSIS-DTS Package search presents three choices:

  • SSIS-DTS Package Search for SQL Server 2000 on Windows x86
  • SSIS-DTS Package Search for SQL Server 2005 on Windows x86
  • SSIS-DTS Package Search for SQL Server 2008 on Windows x86

Although there are really only two different versions of the executable, I wanted to give a SQL Server 2000 choice to make it clear that DTS is still supported. Actually both versions support DTS packages. The reason for the two versions is to match the SQL Server client tools installed for the newer SQL Server releases, 2005 and 2008 respectively.

SSIS-DTS Package Search for SQL Server 2000 on Windows x86 is the version that supports SQL Server 2000 DTS packages. There is not a version that only supports DTS packages. This choice will download SSIS-DTS Pacakge Search for SQL Server 2005. It will install and run with out issue on a computer that only has the SQL Server 2000 client tools installed.

SSIS-DTS Package Search for SQL Server 2005 on Windows x86 is the version that supports SQL Server 2000 DTS packages and SQL Server 2005 SSIS packages. This version can be installed on a computer that has both the SQL Server 2000 client tools and the SQL Server 2005 client tools. It can also be installed on a computer with only the SQL Server 2005 client tools and support DTS as long as the Backward Compatibility components are installed.

SSIS-DTS Package Search for SQL Server 2008 on Windows x86 is the version that supports SQL Server 2000 DTS packages, SQL Server 2005 SSIS packages and SQL Server 2008 SSIS packages. This version can be installed on a computer that has both the SQL Server 2000 client tools and the SQL Server 2008 client tools. It can also be installed on a computer with only the SQL Server 2008 client tools and support DTS as long as the SQL Server 2005 Backward Compatibility components are installed.

Is DTS Dead?

IS DTS dead?

No.

The reasons:

The huge installed base of Sql Server 2000, some of which may never be upgraded.
The huge number of DTS packages that have already been written, some of which will never be converted to Integration Services.
The continued support for DTS in Sql Server 2005 and 2008.
The Execute DTS 2000 Package Task in Integration Services.
The ability to upgrade to Sql Server 2005 and leave the DTS infrastructure untouched.
The popularity of DTS Package Search.

How is DTS living in your organization?

Follow

Get every new post delivered to your Inbox.