> 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
>

"Allan Mitchell" wrote:

> 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
>
>
> > 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
> >
>
>