Hello,
I use DTS to transfer some tables approximately around 20million rows from
as400 to SQL.

Once I transfer 20million rows, the next time I only need to transfer
updated data in AS400 not all the rows again.

DTS wizard only has drop table in SQL then copy the same table from as400 to
SQL again option.Or thats all I know.

I would be thankful for any help.

regards.

Rifat
Microsoft Turkey
Academic

You are going to need to handle this yourself as there is no option as you
have noticed to say "New Data Only"

So how can you do that?

1. Have a table in the AS400 side that logs a row everytime something is
changed on the AS400. You then use this at ETL time to decide which rows to
bring across

2. Create a linked server of the AS400. You can then query it something
like another other SQL Server and this will make it easier to decide what
rows to bring across.

3. Have a flag on the AS400 tables to say something like "IsDirty". This
will identify rows that require moving at ETL time. At the end of the ETL
process you simply set them back to not being dirty.


There are more ways to do this as well but some of them involve lookups and
on a 20 million row table this will hurt you



"rifat@msakademik" <rifat@msakademikDOTNET> wrote

Rifat,
If you cannot change the structure of your tables on the AS400 side, the
best thing to do would be to create the linked server and query for new
fields using a "not exists" clause to get new data, and a join to get
changed data.

Beware though, accessing AS400 from DTS using Client Access drivers/OLE/ODBC
can result in poor performance. If you have the capability to do so at the
application layer, I'd reccomend using IBM's .NET data provider for AS400
DB2. It provides excellent performace.
John Glass


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Have a look at StarQuest Data Replicator (previously sold by
Microsoft!), it replicates new data only from DB2 to SQL Server.

http://www.starquest.com/Productfolder/infoSQDR.html

Good luck,

Bob