The Gerasus Software Blog

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 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 (right click the link and click Save Target As…\Save Link As..).

The SQL Server 2008 version is available here (right click the link and click Save Target As…\Save Link As..).

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.

My primary development machine for the past 3 years was a 32 bit Toshiba Satellite notebook running XP named PROBE-DROID. I was very happy with the machine. It went through several upgrades, more memory and a new 7200 RPM Hitachi hard drive. The purpose of PROBE-DROID was a portable virtual development and testing computer lab. I used VMWare Server 1.x for clean build and test machines. I was very happy with VMWare Server 1.x. The user interface integrated well with Windows. The only major problem I had was with one of the point upgrades breaking the network in some way so that the host OS could no longer access the internet. After a few hours of troubleshooting and research, I decided it would be better to go back to the previous version and get on with the development of DTS Package Search. A few months later VMWare Server 2.0 was released and I upgraded. I was not very happy to see Java and Tomcat with a web based interface. Of course the inevitable Java security update caused Tomcat stop the VMWare web interface from working. Uninstalling the security update had no effect. Instead of wasting precious development time trying to fix this, I reinstalled VMWare Server 2.0 and all was good again. I refrained from installing any Java updates after that.

I had no need or interest in moving to Vista, so I carried on. As PROBE-DROID aged the battery life degraded to the point where it couldn’t make it an hour with out a recharge. I needed to replace the battery or move on to something new. With the release of Windows Server 2008 and Sql Server 2008, it became clear that I needed to support the newer platforms and features. I stumbled across several web sites that discussed running Windows Server 2008 as workstation. They claimed that it performed much better than Vista yet with all the bells and whistles. I upgraded the 64 bit PC at home to from XP to Windows 2008 “Workstation” to test this configuration. http://www.win2008workstation.com/ was particularly helpful with its automated tool to turn on all the Vista goodness. I also wanted to take a look at the Hyper-V role, but the PC lacked the Virtualiztion Technology feature that Hyper-V required.

At that point rather than buy a new battery for PROBE-DROID, I decided to get a new 64 bit notebook. Initially I research Intel’s web site to find the CPUs that supported Virtualization Technology. I purchased an HP notebook with a CPU the met the requirements. After installing Windows Server 2008, I had no luck with the Hyper-V role. Virtualization Technology needs to be handled by the BIOS as well. After bouncing around HP support and sales, I found someone that knew the answer. I needed a notebook that was branded vPro.

The first HP was returned. In order to get a vPro branded notebook, I needed to move to a business class machine. The HPs and Dells were all around $3000 for an Intel T9400/4GB/200GB 7200 RPM configuration which was much more than I wanted to spend. I decided to take a look at Toshiba model and found a Tecra A10 with the right parts for less than half. Since the new notebook was the much more potent 64 bit son of PROBE-DROID, I christened it PROBE-DROID64.

PROBE-DROID64 came with Vista. The plan was to wipe and install Windows Server 2008, but with the beta release of Windows Server 2008 R2 I decided to go that direction. That decision turned out to be a rough road as the dreaded BSOD would appear randomly will running a Hyper-V VM. The performance of the terminal service sessions was also poor with lots of screen flashing while navigating through Visual Studio 2008. I was almost ready to move off R2 beta when the RC was announced. I went the upgrade path to move from beta to RC. The RC release fixed everything and now I have a stable and well performing portable virtual development and test computer lab. I look forward to the RTM of Windows Server 2008 R2. I will discuss my experiences using Hyper-V in another post.

Denny Cherry wrote a blog post recently discussing whether to run anti-virus software on Sql Server machines. He said you should and I agree. It is just too risky not to run anti-virus with all of the notebook computers coming in and out of the office. The post did remind me of something that happened to a Sql Server cluster a few years back was an “if it can happen, it will happen.” The anti-virus vendor pushed out a bad virus signature file that flagged sqlservr.exe as a virus. They realized the mistake and push out a corrected file after the bad one was out for a only short period. Of course our cluster had pulled bad file. Of course sqlservr.exe was found on the passive node and quarrentined. Of course the cluster failed over, or tried to. We were a bit perplexed why sqlservr.exe had disappeared, but figured out where it went and got things running with out too much down time.

When creating a table with a column that will hold the name of a stored procedure that feeds an EXEC command, make sure the data type of this column is sysname. The data type of this column should not be VARCHAR(50) or some other arbitrary data type that should just do the job. This will also be true for columns that hold table and column names. Any column in a user table that holds meta data from a Sql Server system table should match the data type of the source system table column.

I’ve been working on performance tuning a stored procedure that has many optional parameters and is dynamicized by using the always optimizer confusing COALESCE in the WHERE clause (along with other SARG hiding constructs that cause indexes to be ignored).

The stored procedure is called with a date range defined in the parameters @StartDate and @EndDate which are optional.

<code snippet>
, @StartDate DATETIME = NULL
, @EndDate DATETIME = NULL
</code snippet>

The parameters are used in the WHERE clause with a COALESCE.

<code snippet>
WHERE StartDate >= COALESE(@StartDate, StartDate)
AND EndDate <= COALESE(@EndDate, EndDate)
</code snippet>

Using this technique causes the query optimizer to ignore the indexes that have been created on the StartDate and EndDate columns.

One method to fix this problem is initialize the @StartDate and @EndDate with some minimum and maximum dates that make sense for the data being queried as the parameter defaults.

<code snippet>
, @StartDate DATETIME = '01/01/2009'
, @EndDate DATETIME = '01/01/2010'
</code snippet>

Another way is to initialize the parameters in the procedure body for the case where the default dates need to be calculated.

<code snippet>
-- If the date parameters are not supplied,
-- make them 1 month in the past and today
-- to make the query optimizer happy
IF @StartDate IS NULL
SET @StartDate = DATEADD(MONTH, -1, GETDATE())

IF @EndDate IS NULL
SET @EndDate = GETDATE()
</code snippet>

The WHERE clause for either of these cases would change.

<code snippet>
WHERE StartDate >= @StartDate
AND EndDate <= @EndDate
</code snippet>

Now the query optimizer has good SARGs and will do a much better job utilizing those indexes that are using all that space in the database.

Using this technique resulted in a 400% performance improvement for the stored procedure that was being tuned.

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?

Well, I am blogging.

I’ll be blogging about DTS Package Search and all things Sql Server along with a splash of C# programming as it pertains to DTS and SSIS.