DTS step is insanely ssllooww...

[OhioSteve]

I am importing data from Oracle to SQL Server. I use a "data driven query task" to do this. The icon looks like a gold cylinder with a magnifying glass.

It is INCREDIBLY slow. It takes 30 minutes to move 250k records. Both servers are almost new, the tables are indexed, and I'm doing the task when network traffic is very low.

I can't show you my desktop. But I will show you my notes for making the "data driven query task".

Please suggest an approach that would speed up the transfer.

_______________________________________


Source Tab-
Select Oracle connection.
Select table. Don't use "sql query" option.

Bindings Tab-
Choose sQL connection.
Choose SQL table.

Transformations Tab-
Choose transformation name. Edit it.
[now entering second set of tabs. The tabs are general, source, and bindings]
Choose source and binding columns.
Under general tab, click properties. Choose active-x script. Use auto-gen to make script.
[now returning to first set of tabs.]

Queries tab-
Choose build.
select sql server table. Put a mark in each field, so that fields are added to insert sql text.
"values" part of insert sql will have a series of commas. replace commas with question marks.

Lookups-
no changes

Options-
no changes

 

[mrdenny]

Ditch the data driven query task.

Add two connections. One for the SQL Server, and one for the Oracle server. Setup a transfermation between the two servers.

This should run faster.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)