Hello,
my task is to transform data from source table located in db on SQL
Server table to the destination table on DB2.
I've implemented a simple DTS package using 2 OLE DB Connections and
Transform Data Task (TDT) set between them. At first I tried to load
all records from source in one DTS execution, but it ended up with out
of memory problem.
I guess some resultset implementation which is used by OLE DB caches
records that has been already read from db in memory. If I am right can
this behaviour be changed by turning off caching?
In another approach I decided to load records in portion. I've
implemented second DTS package that consists of one ActiveX Task. This
task executes the previously implemented package in a loop passing the
portion parameters (in global variables) which are used in WHERE
condidiotn of the SQL query in TDT.
The SQL query is simple as this "select t.* from table t where t.id
with out of memory error too. When the first portion is processed I can
see records appearing immediately in the destiantion table and MMC
memory usage grows (to the level "A"). After the portion is finished
the memory usage goes down. Then the second portion is processed. At
the beginning I can see the growning memory usage and no new records
appearing in the destination table. That's the phase when TDT fetches
previously processed records. When the memory usage reaches the level
"A" (all of previous records are fetched) TDT starts processing actual
portion of records. The memory usage grows further (to the level "B")
and records from new portion are appearing in the destination table.
And so on ...
It seems the TDT for some reason fetches not only records satysfying
current 'where' condidion, but also those from previously analysed
portions. Am I doing something wrong? Can this behaviour be changed?
What is the best way to export large volumes of data through DTS?
Regards,
Michal