Setting DTS Application Properties
For users of Integration Services who still need to run Data Transformation Services (DTS) packages, Microsoft provides both an updated version of the DTS run-time engine and an updated version of the DTS designer. For information about obtaining and installing these components, see How to: Install Support for Data Transformation Services Packages.
However, the SQL Server 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.
Data Transformation Services (DTS) is deprecated. For more information, see Data Transformation Services (DTS).
The following DTS application properties are discussed in this topic:
Support for the Repository is limited in SQL Server. For more information, see the section, "Support for Meta Data Services (Repository) Packages," in Support for SQL Server 2000 DTS in SQL Server 2008 R2.
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:
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:
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
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).
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
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.
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. There is no equivalent to the Enable Save to Meta Data Services option in the DTS object model.
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
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