Re: Transfering only updated data (from as400 to SQL2000)

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 in message
news:%23EqH8fioFHA.2080@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>


.

Relevant Pages

  • Re: Transfering only updated data (from as400 to SQL2000)
    ... Rifat, ... Create a linked server of the AS400. ... > will identify rows that require moving at ETL time. ... >> as400 to SQL. ...
    (microsoft.public.sqlserver.dts)
  • RE: 0x80004005 - you need permission to view its data
    ... When creating the Access linked server, make sure the data source using UNC ... the SQL server(Both SQL authentication and Windows Authentication work). ... 262177 How to enable Kerberos event logging ...
    (microsoft.public.sqlserver.security)
  • Linked Servers - Real Post
    ... I am running SQL 2k on Win 2k. ... a front end for our SQL database. ... added the linked server I want, and SQL queries it fine, ... leaving the "local server login to remote server login" ...
    (microsoft.public.sqlserver.programming)
  • Linked Server - Issue
    ... I have to access a Linked Server S2 (SQL 2000). ... In S1 I have setup a linked server using sp_addlinkedserver pointing to S2. ... Both MyAccount and SpecialAccount has access permissions to S2. ... Reason Not associated with a Trusted SQL ...
    (microsoft.public.sqlserver.programming)
  • Re: Connecting to FoxPro data
    ... Since you want to "mix" the Fox tables with the SQL tables it sounds like ... Note that when you select data from a VFP linked server you need the three ... the data source string in the example is for a FoxPro DBC or "database ...
    (microsoft.public.sqlserver.connect)
Loading