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..).

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.