Refreshing DTS Caches

version: 1.0.0.0
last updated: 14 February 2002

Starting with SQL Server 2000, DTS maintains caches to improve the startup time of the DTS Designer and the DTS Wizard. Instead of enumerating the available OLE-DB providers, Active/X scripting engines, DTS Tasks and DTS Transforms on the fly from the Registry using COM Catalog enumeration, this information can be read from a cache.

By default the cache is turned off and can be enabled through the SQL Server Enterprise Manager UI, by right clicking on the "Data Transformation Services" node in the left-hand tree. This will bring up the "Package Properties" dialog which looks like this:

Checking the "Turn on cache" checkbox will enable the caching, you can refresh (rebuild) the caches by clicking the "Refresh Cache" button.

These activities can also be performed programmatically, by using the DTS Package Application object, which was introduced in SQL Server 2000.

The Application objects contains four collections OLEDBProviderInfos, ScriptingLanguageInfos, TaskInfos and TransformationInfos. Each of these collections a property to turn on/off caching per collection and a method to Refresh the cache. Through the programming model you have finer grain control over which cache gets turned on and which one gets refreshed, while through the UI it is an all or nothing decision.

The following example shows how to selectively refresh the caches that are turned on using Visual Basic 6.0

' ********************************************************************************
' @file         RefreshCaches.bas
' @description Demonstrates how to refresh the 4 DTS caches in SQL Server 2000
' @copyright    Copyright © SQLDev.Net 1991-2002 All rights reserved.
' @author       GertD@SQLDev.Net
'
' @version @date      @change
' 1        2002-02-14 project creation
' ********************************************************************************
'
' Add reference to Microsoft DTSPackage Object Library
' C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSPkg.DLL
Option Explicit

Public Sub main()
    On Error GoTo errHandler

    Dim oApplication As DTS.Application
    Set oApplication = New DTS.Application

    ' check if cache is turned on, if turned on refresh the cache
    '
    If oApplication.OLEDBProviderInfos.UseCache Then
        oApplication.OLEDBProviderInfos.Refresh
    End If

    If oApplication.ScriptingLanguageInfos.UseCache Then
        oApplication.ScriptingLanguageInfos.Refresh
    End If

    If oApplication.TaskInfos.UseCache Then
        oApplication.TaskInfos.Refresh
    End If

    If oApplication.TransformationInfos.UseCache Then
        oApplication.TransformationInfos.Refresh
    End If

    Set oApplication = Nothing

Exit Sub

errHandler:

Dim rc As VbMsgBoxResult
rc = MsgBox(Err.Number & " " & Err.Description, vbCritical + vbAbortRetryIgnore, "Error")
If rc = vbAbort Then Stop
If rc = vbRetry Then Resume
If rc = vbIgnore Then Resume Next

End Sub

Download sample RefreshCaches.zip using Visual Basic 6.0

Download sample RefreshCaches.zip using Visual Basic Script

Turning on/off caches happens by simply setting the property UseCache on the collection object like:

oApplication.TaskInfos.UseCache = True

will turn on the cache for the DTS Custom Tasks. The cache information is stored in the Registry under:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration

Since the location where the information is stored resides in HKEY_CURRENT_USER, this implies that the caches are maintained on a per user basis. Each cache is stored under a sub key:
    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration\OLEDBProviders
    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration\ScriptingLanguages
    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration\Tasks
    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration\Transformations

Under each of these sub keys you will find a key per registered object, the object is identified by its GUID which is used as the name of the registry key. Per cache different information is stored.

When you register a Custom Task via SQL Enterprise Manager, the task will be automatically added to the Tasks cache (when the cache is enabled). Since there is no way to register Custom Transforms only registering COM server implementing the transfrom with regsvr32.exe and updating the cache will make the new transform available in the DTS Designer or DTS Wizard.

When you register a Custom Task using regsvr32.exe C:\MyCustomTask.dll, the cache will not automatically get updated. The same is true when adding OLE-DB providers or scripting engines when the cache is turned on. Only rebuilding the cache with the Refresh() method or through the UI will make the new component available in the UI.

***