RE: DTS Designer problems after upgrade to SQL Server 2008

This turned into a support call to MS and, although considerable time was
spent on it, there isn’t any good resolution.

The main problem is; when attempting to open a DTS package in SSMS on
Windows Server 2003 after upgrading to SQL Server 2008 the following error
occurs:

SQL Server 2000 DTS Designer components are required to edit DTS packages.
Install the special Web download, "SQL Server 2000 DTS Designer Components"
to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

We tried the following:

• The Standard version installation of SQL Server does not include a
ssms.exe.manifest file. We tried using one from an Enterprise installation
with no success. KB 917406.
• Created a registry key [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft
SQL Server\80\Tools\ClientSetup]
"SQLPath"="C:\\Program Files(x86)\\Microsoft SQL Server\\80\\Tools" and
checked the environment variable path, the location C:\\Program
Files(x86)\\Microsoft SQL Server\\80\\Tools existed. Rebooted with no
success. KB 919131.
• Uninstalled SQL Server 2005 Backward Compatiblity and SQL Server 2000 DTS
Designer Components and reinstalled them. No success.
• Checked the environment variable path. The location C:\\Program
Files(x86)\\Microsoft SQL Server\\80\\Tools should be before the 100 folder.
Made the change and now we were able to open the package.
• We also had issues with the icons in the designer not being displayed
correctly. No resolution was achieved. KB 917406.
• Attempts to use the DTS Designer on Vista and Windows XP failed similarly
as above with no resolution. Apparently there is internal documentation at
MS which states it will not run on Vista. Service Pack 3 for SQL Server 2005
is supposed to contain a fix for this. We tried disabling DEP as mentioned
in the documentation of SP3 with no success.
• We discovered later that we could only open a package once after the
MSSQLServer service had restarted. Any subsequent attempts would fail with
the original error until the service is restarted.

It would appear the only viable solution would be to wait for the release of
SP3 or upgrade all DTS packages to SSIS which we have chosen to do.

--
TomM


"Mark Han[MSFT]" wrote:

Hi,

This is Mark again; I'm writing to follow the issue.

How about the issue now? If it is convenient to you, could you please tell
me the status of the issue?

If you need further assistance, feel free to let me know. I will be more
than happy to be of assistance.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================


.

Relevant Pages

  • Re: SQL Server 2000 DTS Designer Components
    ... I actually have not installed the DTS Designer on the servers directly, but install it on client machines of programmers and administrators who manage the DTS packages. ... Having said that, I see that on my 64-bit server installation, the SQL Server Management Studio 2005, etc. are all x86 programs so the DTS Designer should work even there. ...
    (microsoft.public.sqlserver.clients)
  • Re: Data transformation service (DTS) in MS SQL
    ... there is an add-in pack for SQL Server ... 2005 that allows one to continue to author and maintain DTS packages ... lets you continue to run your existing DTS packages, ... there is no DTS designer in 2005. ...
    (comp.sys.hp.mpe)
  • RE: Recover SQL files & DST packages from server backup
    ... Move Databases Between Computers That Are Running SQL Server ... In order to backup and restore the DTS Packages, it depends on how they are ... Microsoft CSS Online Newsgroup Support ... This newsgroup only focuses on SBS technical issues. ...
    (microsoft.public.windows.server.sbs)
  • RE: Recover SQL files & DST packages from server backup
    ... > after you install the OEM copy on the SBS box. ... If the DTS Packages are saved to SQL Server, they would be saved in the ... > sysdtspackages table in the SQL Server msdb database. ...
    (microsoft.public.windows.server.sbs)
  • Re: IF Statement causes Invalid Column Name
    ... This has to do with parse and compile time versus execute time. ... And also deferred name resolution. ... If you refer to a column which doesn't exist, SQL Server will not be able to parse your SQL, so you will never ... SQL Server ...
    (microsoft.public.sqlserver.programming)
Loading