DTS Issues | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTS Issues

Has anyone else experienced anything like this? I am attempting to import an Oracle database into SQL2K using DTS. I am running SQL2K sp3 and the Oracle DB is 8.0.6. My server is a quad processor with 2GB of RAM running Win2K. When I try to import the tables the DTS appears to generate 4 threads which I can see in the DTS window. I am getting errors such as "connection is busy with results for another command" and the system locking up. I am presuming this is something to do with the multiple processor’s causing problems. Occasionally it will work OK though. Is there anyway I can restrict DTS to use just the one processor? I have tried ticking only one processor in the server properties. I have tried setting 1 processor for parallelism.
This is not necessarily a result of multiple CPU’s. The same DTS would likely spawn 4 threads even on a single CPU system if that is how you have designed it. Note that it is saying the connection is busy, try reviewing your DTS design to see if there are any tasks which need to be done in succession. You can modify the DTS to run appropriate tasks serially where required.

DId you find any locks on SQL Server or ORacle during process? _________
Satya SKJ

No I did not see any locking on either database. I have found that it appears to spawn 4 threads of execution regardless of what processors are available to it. I found this out because when I restricted it to 1 CPU there were 4 threads and the CPU maxed at 100%. Increasing the CPUs back to 4 took the load off. The number of threads per DTS job remained at 4 regardless. However, it is still very frustrating how these 4 threads cross over during the processing and appear to confuse each other. Occasionally when processing big tables it will appear to be doing parallel processing well but then all of a sudden it will get confused and give errors for several tables before stabilising again. Its as if one thread is doing a job that is dependant on another thread before the first thread has finished.
Try to capture PERFMON counters on SQL server for assesment of process.
Also PROFILER for slow running queries. _________
Satya SKJ