I have a SQL 2000 SP4 server running on Windows 2003 SP2. About 6 months ago I started experiencing problems editing existing DTS packages. At that time it was just 1 package. (I tested that I could edit all others but this one package the first time I experienced the problem.)
But now it seems like anytime we experience import problems, I cannot edit the package so that I can verify that the import file is good. (So far the initial problem is always with the import file and once we figure out what that is and resolve it the package runs ok again.)
The problem is I'm planning on migrating to SQL 2005 and since I can't import DTS packages, I'm going to have to rewrite them as Integration Service Projects. If I can't edit the workflow tasks in DTS, it will be time consuming figuring out what the actual import file is. Also, most of these imports are coming from our AS400 which we are phasing out so I have to rewrite these to work with the new ERP package we are going to as we bring on each division.
I have not installed any post SQL 2000 SP4 hotfixes. So if anyone is aware of a hotfix that resolves this issue, please let me know.
Have your permissions changed?
Can you open them in design view at all?
SQL Server 2005 does have backward compatability for DTS packages; it allows you to have them on there, but does not allow you to create them. I believe you need a service pack update to be able to do this though.
I can open the package but when I right click on the workflow task and select properties, SQL Server Hangs. I have to forcibly close Enterprise Manager. The only error I get is an Application Hang error in event viewer.
The problem with the Import file is resolved. The job now runs successfully, however I still cannot edit the workflow tasks. It cannot be a permission problem, nor is it a problem that the server no longer exists. It appears to be a problem with the DTS Editor. Also, I can copy the job and it runs but I still cannot edit the copy.
In this case "Administrator". In other import jobs, I am listed as the owner. It appears that any of the jobs where I import from the AS400 cause Enterprise manager to hang. I use a "special" account on the AS400 to access the information. I suppose it is possible that the account still has "execute" rights but may not have browse rights? These jobs were created years ago and only fail when our AS400 goes down. (yes, our AS400 is ancient and goes down frequently.)
There is an add on for SQL2005 SQL Server Management Studio that lets you see DTS packages and edit them under this version. It works really well.
Install it, then make a connection to your sql2000 server (under database engine) then select Management, then legacy folder, all of your dts packages should appear, then migrate the packages to DSTX files and open under microsoft visual studio.
Then you can edit away...... ( : (utility is listed under here)
here is the tool http://www.microsoft.com/downloads/d...displaylang=en
Microsoft SQL Server 2000 DTS Designer Components
The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.