Partilhar via

Setting DTS Application Properties

  • Artigo

New: 5 December 2005

For users of SQL Server 2005 Integration Services (SSIS) who still need to run SQL Server 2000 Data Transformation Services (DTS) packages, Microsoft provides an updated version of the DTS runtime engine as part of SQL Server 2005 Integration Services (SSIS) Setup, and an updated version of the DTS designer as part of a Web download. The SQL Server 2005 tools provide access to almost all DTS features through the familiar designer and run-time engine.

However, the SQL Server 2005 tools no longer provide access to the application-wide settings in the DTS Package Properties dialog box, which was accessed in SQL Server 2000 Enterprise Manager by selecting Properties from the popup menu on the Data Transformation Services node. This topic describes these settings and how to configure them manually, by editing the applicable Registry entries, or programmatically, by calling the corresponding methods in the DTS object model that modify the Registry entries for you.

DTS Application Properties

The following DTS application properties are discussed in this topic:

  • Cache: Turn on cache (check box)
    Caches lists of the available tasks, transformations, scripting languages, and OLE DB providers to improve performance.
  • Cache: Refresh cache (button)
    Refreshes the cached lists described above.
  • Designer: Show multi-phase pump in DTS designer
    Enables display of advanced options that allow specific phases of a data transformation, such as "Pre source data" or "On transform failure," to be configured individually.
  • Debugging: Turn on just-in-time debugging
    Enables debugging for developers of custom tasks.
  • Meta Data Services: Enable Save to Meta Data Services
    Allows packages to be saved to Meta Data Services (the Repository).

Note

Support for the Repository is limited in SQL Server 2005. For more information, see "Meta Data Services (Repository) Packages" in SQL Server 2005 Integration Services Backward Compatibility.

Setting the DTS Application Properties in the Registry

To enable or disable the following DTS application properties manually, by modifying the Registry directly, set the following REG_DWORD values to 0 (enabled) or 1 (disabled) under the parent key HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Settings:

  • Turn on cache
    TasksCache

    TransformationsCache

    ScriptingLanguagesCache

    OLEDBProvidersCache

  • Show multi-phase pump in DTS designer
    DesignerSettings
  • Turn on just-in-time debugging
    JITDebug

Note

It is not possible to refresh the cache by editing Registry values. The cache can only be refreshed programmatically.

To enable or disable the following DTS application property manually, by modifying the Registry directly, set the following REG_DWORD value to 0 (enabled) or 1 (disabled) under the parent key HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\80\DTS\Settings:

  • Enable Save to Meta Data Services
    EnableSaveToRepository

Warning

The following warning was displayed by the Package Properties dialog box when the Enable Save to Meta Data Services property was enabled: "Warning: Packages saved to Meta Data Services should not be considered secure. Are you sure you want to enable saving packages to Meta Data Services?"

Sample Registry Script

The following Registry script, when saved in a text file with the .reg extension and run, enables all the DTS application properties discussed in this topic. To disable the properties instead, change the new values from 00000001 to 00000000.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Settings]
"TasksCache"=dword:00000001
"TransformationsCache"=dword:00000001
"ScriptingLanguagesCache"=dword:00000001
"OLEDBProvidersCache"=dword:00000001
"DesignerSettings"=dword:00000001
"JITDebug"=dword:00000001

[HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\80\DTS\Settings]
"EnableSaveToRepository"=dword:00000001

Setting the DTS Application Properties in the DTS Object Model

To enable or disable the following DTS application properties programmatically, set the following values of the DTS Application object to True (enabled) or False (disabled).

  • Turn on cache
    .TaskInfos.UseCache

    .TransformationInfos.UseCache

    .ScriptingLanguageInfos.UseCache

    .OLEDBProviderInfos.UseCache

  • Turn on just-in-time debugging
    .JITDebug

To enable or disable the following DTS application option programmatically, set the following values of the DTS Application object to the appropriate value from the DTSDesignerSettings enumeration:

  • Enabled - DTSDesignerSettings.DTSDesigner_ShowMultiPhaseTransforms
  • Disabled - DTSDesignerSettings.DTSDesigner_Default
  • Show multi-phase pump in DTS designer
    .DesignerSettings

The value of the Enable Save to Meta Data Services property cannot be set by using the DTS object model. This property can be set by using the Registry class in the Microsoft.Win32 namespace of the .NET Framework Class Library, as demonstrated in the following sample.

Sample Code

The following code sample for a console application, when compiled and run, enables all the DTS application properties discussed in this topic. To disable the options instead, change the new values from True to False, and change the value of the DesignerSettings property to DTSDesignerSettings.DTSDesigner_Default.

This application requires a reference to the Microsoft DTSPackage Object Library (COM).

The value of the Enable Save to Meta Data Services option can only be set by modifying the Registry directly.

Imports Microsoft.Win32
Imports DTS

Module SetDTSProperties

  Sub Main()

    Const SETTINGS_ROOT_KEY As String = "Software\Microsoft\Microsoft SQL Server\80\DTS\Settings"
    Const METADATASERVICES_VALUE As String = "EnableSaveToRepository"

    Dim dtsApp As New DTS.Application
    Dim keySettingsRoot As RegistryKey

    With dtsApp
      .TaskInfos.UseCache = True
      .TransformationInfos.UseCache = True
      .ScriptingLanguageInfos.UseCache = True
      .OLEDBProviderInfos.UseCache = True
      .DesignerSettings = DTSDesignerSettings.DTSDesigner_ShowMultiPhaseTransforms
      .JITDebug = True
    End With

    keySettingsRoot = Registry.LocalMachine.OpenSubKey(SETTINGS_ROOT_KEY, True)
    If keySettingsRoot Is Nothing Then
      keySettingsRoot = Registry.LocalMachine.CreateSubKey(SETTINGS_ROOT_KEY)
    End If
    With keySettingsRoot
      .SetValue(METADATASERVICES_VALUE, Math.Abs(CType(True, Integer)))
      .Close()
    End With

  End Sub

End Module

using Microsoft.Win32;
using DTS;

class SetDTSProperties
{

  public static void Main()
  {

    const string SETTINGS_ROOT_KEY = "Software\\Microsoft\\Microsoft SQL Server\\80\\DTS\\Settings";
    const string METADATASERVICES_VALUE = "EnableSaveToRepository";

    DTS.Application dtsApp = new DTS.Application();
    RegistryKey keySettingsRoot;

    {
      dtsApp.TaskInfos.UseCache = true;
      dtsApp.TransformationInfos.UseCache = true;
      dtsApp.ScriptingLanguageInfos.UseCache = true;
      dtsApp.OLEDBProviderInfos.UseCache = true;
      dtsApp.DesignerSettings = DTSDesignerSettings.DTSDesigner_ShowMultiPhaseTransforms;
      dtsApp.JITDebug = true;
    }

    keySettingsRoot = Registry.LocalMachine.OpenSubKey(SETTINGS_ROOT_KEY, true);
    if (keySettingsRoot==null)
    {
      keySettingsRoot = Registry.LocalMachine.CreateSubKey(SETTINGS_ROOT_KEY);
    }
    {
      keySettingsRoot.SetValue(METADATASERVICES_VALUE, Math.Abs((int) true));
      keySettingsRoot.Close();
    }

  }

}

The following subroutine can be added to an application to refresh the cache when appropriate. Cached values are stored in the Registry under HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\DTS\Enumeration.

  Private Sub RefreshCache()

    Me.Cursor = Cursors.WaitCursor
    With dtsApp
      .TaskInfos.Refresh()
      .TransformationInfos.Refresh()
      .ScriptingLanguageInfos.Refresh()
      .OLEDBProviderInfos.Refresh()
    End With
    Me.Cursor = Cursors.Default

  End Sub

  private void RefreshCache()
  {

    this.Cursor = Cursors.WaitCursor;
    {
      dtsApp.TaskInfos.Refresh();
      dtsApp.TransformationInfos.Refresh();
      dtsApp.ScriptingLanguageInfos.Refresh();
      dtsApp.OLEDBProviderInfos.Refresh();
    }
    this.Cursor = Cursors.Default;

  }

See Also

Concepts

SQL Server 2005 Integration Services Backward Compatibility
Upgrading or Migrating Data Transformation Services

Help and Information

Getting SQL Server 2005 Assistance