October 22, 2007 at 9:17 am
I am converting a SQL 2000 server to 2005 and I have imported the DTS packages from 2000 by using an IS process to copy them from one msdb to the other. Some of these packages, at least the ones that I checked so far are not opening without errors. They give me an error that they can't be edited because I might not have the DTS editor. I recently imported another one, this time via a structured file, and this one is opening without problems. This last one I can edit and run without a single error. I had an issue before with one that had a space at the end but have since deleted it. Has anyone encountered this problem and found a solution to it?
Thanks,
Ed
October 22, 2007 at 1:27 pm
The errors that I get are the following:
First:
Error Source : Microsoft Data Transformation Services (DTS) Package
Error Description : The DTS host failed to load or save the package properly.
Second:
The selected package connot be opened. The DTS Designer has been closed.
Third:
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 bug is described in Bug#: 345170 and I did edit Sqlwb.exe.manifest as they suggested with no results. I am still getting errors on the older packages and I am still able to modify the last one I imported from a dts file. I tried deleting some of them and importing them from a dts file but I am getting the same error. What's curious is that I used to be able to modify them when I first imported them.
Ed
October 23, 2007 at 7:16 am
For clarification, you've already installed the DTS components from the feature pack, correct?
Tommy
Follow @sqlscribeOctober 23, 2007 at 7:19 am
Hi Tommy,
I did so.
Regards,
Ed
October 23, 2007 at 7:27 am
Do you happen to have MSSQL 2000 tools installed along side 2005's management studio?
Tommy
Follow @sqlscribeOctober 23, 2007 at 7:34 am
No, the new server we are migrating too only has 2005 installed.
October 23, 2007 at 7:45 am
Hmm. I've seen this in the past when both MSSQL 2000 tools and 2005 tools were installed along side eachother...
Running SP2, correct? Could possibly be some issue with the DCOM component registration for DTS. Have you tried reinstalling them?
Tommy
Follow @sqlscribeOctober 23, 2007 at 9:04 am
I've had a similar problem.
My problems were connected with instance names and ensuring that I had a real genuine connection to each server.
Hope this is of help
October 23, 2007 at 12:15 pm
Only my dev server has SP2 but they used to work before in this server.
Ed
October 23, 2007 at 12:18 pm
When I originally moved the packages, I had to edit them and I was able to. I didn't have problems with connections to servers because, since all transactions were going against the same server, I had them set to local. I even had a similiar problem when I tried importing a package that had a space at the end of the name. This time I tried deleting one of the ones giving me problems and importing it from a structured file but it gave me the same error. A previous import using this same method did not fail.
Ed
October 24, 2007 at 2:45 am
There are 2 ways of getting SQL 2000 DTS packages into SQL 2005 or SQL 2008. One wraps the package in SSIS and means the package cannot be edited. The other maintains the package as DTS and allows edit.
If you import the DTS package using the wizard, it gets wrapped in SSIS and cannot be edited. IMHO this more or less makes the package useless.
You need to use structureed storage files to transfer DTS packages to SQL 2005 (or SQL 2008) if you want to edit them. Open the DTS Designer in SQL 2005 and select Open. Navigate to your structured storage file and open it. Then do Save As to save it to SQL Server. You now have the same ability to maintain the package in SQL 2005 as you did in SQL 2000.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 24, 2007 at 5:18 am
Only Dev has SP2 applied but I will have network applied to production and QA.
Thanks,
Ed
October 24, 2007 at 5:23 am
Thanks, Ed. I did the original migration by doing a data flow in SSIS from the old msdb to the 2005 msdb. They were editable then. I have one package which I imported using a structured file and it works great. It's the older ones I can't do anything about them. I even try deleting them from 2005 and opening a dts file but I got the same errors as when I was editing from the loaded versions.
Ed
October 24, 2007 at 6:59 am
Thanks Eduardo, it is good to know that the import wizard also gives you editable DTS.
You mentioned the KB 345170 fix, but have you also seen KB 917406. This is a similar fix that worked at my old place (I did not know 345170 existed...).
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 24, 2007 at 7:07 am
Ed,
I didn't use the Import Wizard. What I did was create a SSIS job that copied the packages from the 2000 msdb to the 2005 one. That's probably the reason why I was able to edit them. I haven't seen the other MS document. Thanks for pointing it out to me.
Ed
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply