Designing legacy DTS packages from within SS2005 Management Studio [Michael B]

Problem solved. Thanks to all who wrote with suggestions.

Here is the full story. Sorry if it is a bit much, but there were several false starts.

Chapter 1:

I had been running SS2000 and had a bunch of DTS packages on my laptop. I installed SS2005 Developer’s Edition on the machine, which wiped out the SS2000 Enterprise Manager.

When I tried to open any of the legacy DTS packages from within Management Studio, I got an error message box opened telling me that I needed to install SQL Server 2000 DTS Designer Components.  Please note that I was not attempting to migrate the DTS packages to SSIS; I was merely trying to open the packages in the equivalence of the package designer used in the older Enterprise Manager.

I found the appropriate page on Microsoft’s site discussing the problem (http://support.microsoft.com/kb/905417) and the site to download the “fix” (http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en). I downloaded the recommended file (SQLServer2005_DTS.msi), installed it, and rebooted.

It was no help; symptoms were unchanged.

Chapter 2:

One of our colleagues suggested that I uninstall and reinstall SS2005. I was getting close to do this and was in the Add/Remove Programs utility when I noticed SS2005 Backward Compatibility Components item in the currently installed programs list for removal or repair. I ran the repair.

That didn’t fix it, but it changed the symptoms somewhat. Now when I tried to open a DTS package within SS2005, I got 3 separate error message boxes in succession (I believe the third was the same as the single error message I was originally getting (and oddly, sometimes I got just two messages -- the first and third. Also the package tried to open but only displayed some of the icons in the design window, usually just the connecting arrows.)

Chapter 3:

Two colleagues had sent a link to another Microsoft article  (http://support.microsoft.com/?kbid=917406), (and one of those also suggested I install the Backward Compatibility Components which I had already done, as mentioned above) and now, with the 3 error message boxes, my symptoms matched what was discussed on the Microsoft page.

The support page gave two workaround methods. The second one was in effect to reinstall the client components from SS2000. The first method promised to allow the correct opening of DTS packages from within Management Studio, which was my preferred route.

It said to remove the following lines of code from the Sqlwb.exe.manifest file in the \Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE folder.

The way the lines actually display on the Microsoft page, line 3 (red line numbering added by me for reference) is one long line in a small, scrollable text box. In this message, it wraps.

1.
2.
3.               
4.
5.                                                  

When I opened the file for editing I saw something like the following (once again, I’ve added line numbers) with lines 2 and 3 continuing on the right beyond the window width:

1.
2.
3. 4.  

I searched for and found the opening tag and its closing tag mate and deleted those and everything in between as it seemed to instruct on the Microsoft page.

What I didn’t notice was that the line went way off to the right and that there were 3 assemblyIdentity tag groups on that line. Without realizing it, I had deleted all of them. (Of course, I had made a copy of the file before altering it.)

After deleting the lines, SS2005 Management Studio wouldn’t open at all giving a memory location error. When I restored the lines to the file, Management Studio would open again, but I once again, I still couldn’t properly open DTS packages. 

After several additional attempts to edit the file, I finally noticed that there were 3 sets of assemblyIdentity tag groups, only one of which looked exactly like line 3 in the Microsoft text box. I reformatted the lines of the file so that I could more clearly see the logical tag groups. It now appeared in the Wordpad window something like the following (again, minus the line numbering and the bold font on line 7. And again, lines 5, 6, & 7 scroll off to the right, beyond the width of the editing window.) (BWT, I just noticed while writing this email, that lines 5 & 6 are identical, but that’s how they were in the original file.)

1.
2.
3.
4.
5. 6. 7. 8.
9.
10.

What I realized I needed to do was to delete only line 7, keeping lines 3 thru 6,  and lines 8 thru 10. This indeed did the trick. I can now edit DTS legacy packages from within SS2005 Management Studio.

Conclusion:

1. Install or repair SS2005 Backward Compatibility Components using the Windows Add or Remove Programs utility in Control Panel.

2. Download and install SQL Server 2000 DTS Designer Components which is the SQLServer2005_DTS.msi file from http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en

3.Carefully edit \Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\ Sqlwb.exe.manifest  file removing only the tag group that looks like line 7 immediately above.

I hope this is clear and is helpful.

Michael Bergelson
Architect
Hitachi Consulting

BTW, I also noticed that under SS2005 Management Studio, there is no option to view the DTS package run logs on a right click, nor have I found that function anywhere else as yet.