Data Transformation Services (DTS)
August 24, 2000
These answers are primarily a compilation of multiple newsgroup postings, with or without enhancements derived from my own experiences. I make no guarantees as to the suitability of any of these solutions, so use at your own risk. I also advise you to try the Microsoft Support Knowledge Base (http://support.microsoft.com/).
"Could not create component categories manager" Or "Could not create an instance of the DTS package"
This can happen after some additional software has been installed, and DLLs required by DTS have been overwritten.
The main culprit is Comcat.dll (winnt\system32). Version 4.71 is required for DTS to function correctly.
For a quick fix copy Comcat.dll from your SQL 7 CD-ROM, and register it as described below. If this does not
work then start working your way through the suggestions below.
"DTS WIZARD ERROR: CoCreateInstance - Class not Registered"
This is another case of DLLs having been overwritten. Quick fix is to try re-registering DTSPkg.dll first.
"The parameter is incorrect" Or "Invalid class string" Or "The system cannot find the file specified"
This is caused by incompatibilities between the DTS Package format of SQL Server 7 RTM or SQL Server 7 Service Pack 1 and SQL Server 7 Service Pack 2 or SQL Server 2000.
You cannot edit or run a package on a pre-SP2 machine once a SP2 or SQL 2000 machine has saved it, unless you use a package password. The standard format used to save packages has been changed after SP1. The encrypted package format however, is the same, so using a package password will bypass the issue. Alternatively open a version of the package prior to the SP2/2000 edit, and only save changes using RTM or SP1 tools.
The "The system cannot find the file specified" message is specific to SQL 2000, for which the standard format has changed again from that of SQL 7.0 SP2. The use of a package password will overcome this issue as well.
A quick summary of compatibility for reading and executing packages is outlined below:
In summary, ensure all SQL 7.0 Servers/Clients have SP2 applied, and use package passwords if you have SQL 2000 mixed in as well.
"Invalid class string" is not strictly a SQL Server error in DTS. Essentially it means in cannot find the CLSID for a required object when reading the package. This error can also occur when loading a package that contains a CustomTask which has not been registered on the current machine.
"The license for the installation of Microsoft SQL Server on your source and destination connections does not permit the use DTS to transform data. Please refer to your license agreement for more information."
This message will appear when trying to transfer data between SQL Server Desktop and a Standard, Enterprise or SBS Server. From SQL Server Books Online, "SQL Server Editions" :
Microsoft. SQL Server comes in three editions: Standard, Enterprise, and Small Business Server (SBS). In addition, users covered by a per-seat license from any of these three editions can install a Desktop SQL Server installation on their client computer.
Put simply this means that the non-Desktop installation of SQL Server is using the Per-Client licensing method, and your are therefore not entitled to use the Desktop edition so I'm not going to let you transfer this data! You can change the licensing mode from via the Control Panel.
"Run-Time Error -2177221499 (80040005) Provider generated code execution exception: EXCEPTION_ACCESS_VIOLATION" when executing a Package from Visual Basic
Visual Basic is an apartment-threaded application and DTS is free-threaded and the two can conflict to produce the error above. There is a simply overcome by setting each step's ExecuteInMainThread property to true. To do this manually Right-Click a Task and select Workflow Properties, and then select the Options sheet. In the Execution frame you will see the "Execute on main package thread" option which needs to be checked. This can also be done programmatically, see How to Execute a DTS Package from... for an example of this.