I'm having an issue with a DTS package that I created. It's running on a
Windows 2000 Server, XEON processor, 2.5 GB of RAM, SQL 2K with service pack
3. The destination database in is simple mode.

The package consists of two Microsoft OLE DB Provider for SQL Server
connections. Both connections are to databases on the same server but
different drives. They are connected two different databases that are
located on separate drives. This is a single transform data task between the
two of them...

The source part is pulling approximately 180,000 records. The destination
is an table that has a primary key and 8 foreign keys and already contains
records that are not going to be "touched" by the data task.

There are 7 lookup queries to tables that exist on the destination database,
all but have cache set a 100 or above (no higher than 500).

The transform task contains four ActiveX scripts using VBScript to handle
the data transformation.

The issue is that is it took 6 hours to migrate the data from one database
to the other database. The task started processing records with two minutes
of launching the package. The rest of the time is processing the records; it
takes about 3 minutes to process 1000 records.

Has anyone had this kind of problem? If so, were you able to find a
solution to make the processing go faster? Any assistance or insight would
be greatly appreciated.

Thanks

Hello scuba79,

So which part is taking the time?

The fact that you have 4 AX transform scripts in there with 7 lookups I would
expect that to hurt. Granted what you are seeing is slightly longer than
I would expect but I do not know what your Lookups and AX Script transform
look like.

This architecture would cause every single row to be processed individually.

Can this not be achieved in TSQL more elegantly?


Allan

Allan,

Unfortunately this is a process that I inherited from a previous
developer... The part that is taking the longest is the processing of the
records. The lookups are basic queries with a parameter, only one of the
queries requires two parameters. The parameters are integers. The ActiveX
scripts are checking over the data to add "new" values that are no more than
an integer or a single character.

I excpeted some time for the processing but not 6 hours

Scuba

"Allan Mitchell" wrote: