Incremental Data Transfer using DTS

Question

Post reply

Incremental Data Transfer using DTS

venu menon

SSC Rookie

Points: 31
More actions
October 25, 2005 at 1:34 am

#76444

Hi Friends,
An issue which has been nagging me since few days is pertaining to Data Transfer using the DTS. I have got a voluminous data in Production DB (DB 2), which I need to transfer to Development DB (SQL Server 2K). For this DTS packages for set of tables has been defined and are in place. But the issue now is that data latency between the Production DB and Develpment DB has to be 24hrs. The packages are run daily at the end of the day. The current design of DTS deletes the data and then inserts the data into the tables.But some tables have got enormous amount of data, due to which the data insertion takes lot of time (infact it goes on for 12-20 hours) which is not permissible.
I would like to know if there is any way wherein we can transfer only the incremental data (i.e. whatever is added/modified/deleted) on a daily basis using the DTS.
Regards,
Venu

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Read up on data driven task. This link will give a summary:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_elemtsk1_9w2z.asp

However....Can be very slow to perform, but should be quicker than deleting and reinserting data.

------------------------------
The Users are always right - when I'm not wrong!

Ralf Bohne SSCommitted Points: 1531 More actions · Answer 2

hi venu,

we transfer 20 millions of rows from an iSeries DB2 system to SQL-Server. It usually takes 90 minutes. we use a drop/ceate table before insertion.

Ralf

venu menon SSC Rookie Points: 31 More actions · Answer 3

Thanks for the update Ralf.

Will try this out and get back to you with our update .

Johnson J Mampoozhil SSC Enthusiast Points: 151 More actions · Answer 4

Hi

I have tried it this way and the DATABASE was in SQL Server

In your production database flag the records which are modified or inserted using Triggers/ someother way. And fetch the records for the flag and process it in DTS using Datadriven queries.

The production database has triggers on tables which update a column ( a flag / datetime ). The dts DDQ call sp's which fetch records based on that datetime/ flag

eg. Select * from TABLE where flag_date >= < last run date> the last run datetime will be stored in another table which will be passed to the SP as paramter.

Johnson