DTS Designer problems after upgrade to SQL Server 2008

[TomM]

I upgraded our SQL Server 2005 sp2 installation to SQL Server 2008. The
installation is a 32 bit install on a 64 bit Windows Server 2003 R2. The
install went fine but I am trying to open a DTS package using the designer
and immediately get the following message with no other message following
this:

TITLE: Object Explorer
------------------------------

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)

------------------------------
BUTTONS:

OK

I have the Client Tools Backward Compatibility (64-bit since the 32-bit msi
would not install) component installed as well as the above mentioned SQL
Server 2000 DTS Designer Components.

On my Vista workstation I am having the same problem since upgrading the
server to 2008.

Any help would be greatly appreciated.

[Mark Han[MSFT]]

Hello Tom,

This is Mark, a SQL Server Engineer. I'm glad to assist you with the issue.

In order to resolve the issue, I would like to explain the following:
A) SQL Server 2008 does not include support for the following DTS features:
1 There is no 64-bit design-time or run-time support for DTS packages.
On a 64-bit computer, DTS packages, and
Integration Services packages that run DTS packages, can run only
in 32-bit mode. To run DTS packages in 32-bit
mode, you have to install and use the 32-bit version of the dtexec
utility (dtexec.exe). To install the 32-bit version of the
dtexec utility, select Client Tools or Business Intelligence
Development Studio during setup.

2 There is also no 32-bit design-time or run-time support for DTS
packages on Itanium-based operating systems.
Therefore, you cannot create, view, modify, or run DTS packages on
Itanium-based operating systems.

B) To install the 32-bit version of the Integration Services runtime and
command prompt utilities on a 64-bit x86 computer
During Setup, select Business Intelligence Development Studio or Management
Tools - Complete. Selecting either of these options installs the 32-bit
version of of the Integration Services runtime, the dtexec utility
(dtexec.exe), and other Integration Services tools. There is an article to
share with you:http://msdn.microsoft.com/en-us/library/ms143755.aspx

C) How to: Run a Package Using the DTExec Utility, there is an article to
share with you:http://msdn.microsoft.com/en-us/library/ms138023(SQL.90).aspx

Besides, I do some addistional research for you: There is an article to
share with you:http://msdn.microsoft.com/en-us/library/ms162810.aspx

If you have any questions on the above information, please tell me.

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: ***@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[TomM]

Mark, thanks for the response but I have been through all of the mentioned
references already without success. Because I needed to get this done
relatively quickly I opened a support call to MS and am working with a tech
to resolve this as it has become quite complicated.

I will report our findings and resolution here when I have all of the
information.
--
TomM

Post by Mark Han[MSFT]
Hello Tom,
This is Mark, a SQL Server Engineer. I'm glad to assist you with the issue.
1 There is no 64-bit design-time or run-time support for DTS packages.
On a 64-bit computer, DTS packages, and
Integration Services packages that run DTS packages, can run only
in 32-bit mode. To run DTS packages in 32-bit
mode, you have to install and use the 32-bit version of the dtexec
utility (dtexec.exe). To install the 32-bit version of the
dtexec utility, select Client Tools or Business Intelligence
Development Studio during setup.
2 There is also no 32-bit design-time or run-time support for DTS
packages on Itanium-based operating systems.
Therefore, you cannot create, view, modify, or run DTS packages on
Itanium-based operating systems.
B) To install the 32-bit version of the Integration Services runtime and
command prompt utilities on a 64-bit x86 computer
During Setup, select Business Intelligence Development Studio or Management
Tools - Complete. Selecting either of these options installs the 32-bit
version of of the Integration Services runtime, the dtexec utility
(dtexec.exe), and other Integration Services tools. There is an article to
share with you:http://msdn.microsoft.com/en-us/library/ms143755.aspx
C) How to: Run a Package Using the DTExec Utility, there is an article to
share with you:http://msdn.microsoft.com/en-us/library/ms138023(SQL.90).aspx
Besides, I do some addistional research for you: There is an article to
share with you:http://msdn.microsoft.com/en-us/library/ms162810.aspx
If you have any questions on the above information, please tell me.
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
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Mark Han[MSFT]]

Hi Tom,

Thank you for the update and cooperation.

I will keep following the issue. If anything I can assist you with the
issue, please tell me.

Have a nice day.

Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================

[Mark Han[MSFT]]

Hi Tom,

I am interested in this issue. Would you mind letting me know the result 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: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Mark Han[MSFT]]

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: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[TomM]

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

Post by Mark Han[MSFT]
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
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Paul Shapiro]

I think the same error occurred with SQL Server 2005, and there was a KB
article on how to install the DTS components.
http://support.microsoft.com/?kbid=917406

I did it once or twice, but it's been too long for me to remember the
details. I remember starting with downloading the DTS Designer components,
and installing those, but I think there was another more obscure step.
Search under SQL 2005 DTS and maybe you can find it. No guarantee it still
works in SQL 2008, but maybe.

Post by TomM
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
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)
• 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

Post by Mark Han[MSFT]
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
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Mark Han[MSFT]]

Hello Tom,

Thank you for the time to public the detail troubleshooting step and the
solution.

I understand the solution might not be very perfect for you. However based
on the situation, it might be most appropriate.

If you have any other concerns, please tell me. we welcome your posting
here again.

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: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================