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

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@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23F1V3lioFHA.320@xxxxxxxxxxxxxxxxxxxxxxx
> 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)
    ... You then use this at ETL time to decide which rows to ... Create a linked server of the AS400. ... > as400 to SQL. ... > DTS wizard only has drop table in SQL then copy the same table from as400 ...
    (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)
  • OLE DB provider MSIDXS reported an error. Access denied.
    ... I've added a linked server to a development box. ... I ran the same command on my local Sql box. ... OLE DB error trace [OLE/DB Provider 'MSIDXS' ICommandPrepare::Prepare ... The development sql instance is running under a local system account. ...
    (microsoft.public.sqlserver.fulltext)