
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

How to troubleshoot DTS problems

Alexander Chigrik
chigrik@mssqlcity.com

Should you have problems with Data Transformation Services (DTS), review this DTS troubleshooting checklist to find potential solutions.

*****
1. If you get the error that the license for the installation of SQL Server does not permit the use of DTS to transform data, switch the SQL Server to "per-seat" mode.

This error means that you cannot do distributed operations (for example, replication, data transformation or heterogeneous query services) between MSDE or SQL Server Desktop and Standard/Enterprise version if the Standard/Enterprise version is installed in "per server" mode.

This is from SQL Server 7.0 END-USER LICENSE AGREEMENT:

Note Regarding Interaction between Microsoft SQL Server and
Microsoft Data Engine ("MSDE").
If you use MSDE to access or use the services of Microsoft
SQL Server (for example, replication, data transformation or
heterogeneous query services), you must use Microsoft SQL Server in
Per Seat Mode (that is, each unique Device accessing SQL Server must
have a Client Access License dedicated to it).

Note Regarding Use of SQL Server Desktop.
You may only install and use the SQL Server Desktop component of
Microsoft SQL Server on each Device to which a valid Client Access
License for the Server Software has been dedicated. If you use
SQL Server Desktop to access or use the services of any version
of Microsoft SQL Server (for example, replication, data
transformation or heterogeneous query services), you must use
such Microsoft SQL Server in Per Seat Mode.

*****
2. If you can run DTS package, but cannot schedule it, check that SQLServerAgent service runs under account, which has access to the network resources.

When you tried to schedule DTS package, the appropriate job will run under the user account that the SQLServerAgent service is running under. For example, if SQLServerAgent service runs under the LocalSystem account, you cannot schedule DTS package, because the LocalSystem account does not have network access.

Note. Before scheduling DTS package, the Schedule service must be started.

To start schedule service on the Windows NT choose: Start -> Settings -> Control panel -> Services (choose Startup "Automatic" type) and Start.

To start schedule service on the Windows 2000 choose: Start -> Settings -> Control panel -> Administration -> Services (choose Startup "Automatic" type) and Start.

*****
3. If you get the error: 'DTS Wizard Error - CoCreateInstance Class not Registered' apply the latest service pack or add MAXDOP(1) to the query which will prevent the query being parallelised.

This error happens with parallel queries only. It means that one thread of the query is waiting for a message packet from another. To work-around this, you should apply the latest service pack or add MAXDOP(1) to the query which will prevent the query being parallelised.

*****
4. If DTS package stored in the repository are not listed, try to download the DTS package from the sysdtspackages system table into file on disk for future using.

The text of the DTS package is stored in the PackageData image field of the sysdtspackages system table in the msdb database. To save DTS package in file, you can use textcopy.exe utility.
See this FAQ question for more details:
My DTS package is stored inside SQL Server. Now I cannot open it.

*****
5. Check that you use the latest SQL Server service pack.

To check what SQL service pack are you running, see this link:
How can I check what SQL service pack am I running?

*****
6. If you get error when export a database from SQL Server 7.0 to SQL Server 6.5 using the DTS Export Wizard, try to create destination database before transfer.

This error arises because DTS incorrectly sends SQL Server 7.0 CREATE DATABASE syntax to SQL Server 6.5.

*****
7. If you get 'not enough memory' error when attempting to import a text file larger than the available memory on a Windows 9x client, try to separate the text file into several small files or use a client computer running Windows NT or Windows 2000.

This error arises because DTS Import Wizard calls the command MapViewOfFile in order to display the structure of the file being imported. If the client computer running Windows 9x, the MapViewOfFile call will attempt to reserve a contiguous area of memory large enough to map the entire file.
So, you can get 'not enough memory' error when attempting to import a text file larger than the available memory on a Windows 9x client.

*****
8. If your database contains space in its name and the DTS Designer does not show columns in build query, try to rename the database to eliminate the space in the database name.

This is SQL Server 7.0 bug; SQL Server 2000 does not contain such problems.

*****
9. When you use the "Copy table(s) from the source database" option of the SQL Server 7.0 DTS Import Wizard to import table with a timestamp column, DTS fails.

To work around this problem, you can use the "Use a query to specify the data to transfer" option of the DTS Import Wizard and exclude the timestamp column; you can use "Transfer objects and data between SQL Server 7.0 databases" option of the DTS Import Wizard and specify which table you want to transfer; you can define the destination table to use binary(8) for the datatype for the column which will contain the timestamp.

*****
10. The DTS Import/Export Wizard may truncate column strings that are over 255 characters long when exports column to a text file and the delimited fields are used for exporting.

To work around this problem, you can use fixed fields instead of delimited fields, you can use bcp utility instead of DTS, or you can create the table column by using a text data type.

*****
11. If the DTS package created on MDAC 2.5/2.6 fails on computer with downlevel MDAC (for example, version 2.1), install MDAC 2.5 (or later) on the computer that is to run the DTS package.

*****
12. The DTS Query Builder does not correctly build an SQL statement that begins with the SQL Server inline comment '--'.

To work around this problem, avoid using the inline comments with the DTS Query Builder. Use the '/*...*/' style of comments instead of the '--' inline comments.

*****
13. The SQL Server 7.0 DTS package with an ActiveX script can be truncated to approximately 32,400 characters when is saved to a local repository.

To work around this problem, save the DTS package to a file or to SQL Server instead of the local repository. This is SQL Server 7.0 bug; SQL Server 2000 does not contain such problems.

*****
14. When you use the "Transfer objects and data between SQL Server 7.0 databases" option of the SQL Server 7.0 DTS Export Wizard, data in columns of a table with datatypes of char or varchar larger than 4000 characters will not be copied.

To work around this problem, use the "Copy table(s) from the source database" option of the SQL Server 7.0 DTS Export Wizard instead of the "Transfer objects and data between SQL Server 7.0 databases".

*****
15. The DTS Import/Export Wizard fails if Excel file was open during the import/export operation.

To work around this problem, close Excel file before import/export.

*****
16. You can get a connection busy error if you transfer data using 'Microsoft ODBC Driver for SQL Server' and attempt to preview stored procedures.

To work around this problem, use 'Microsoft OLE DB Provider for SQL Server' instead of 'Microsoft ODBC Driver for SQL Server'.

*****

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc