DTS Select using record position

[Rowan]

Hi there, it has been a while since i have posted. I am in a
situation where I am stumped. I am learning to build a dts package
where I am connecting to a table in an AS400. This database is being
maintained by an outsourced company and therefore I can't change the
table structure or even ask them to. Anyway, this table currently has
about 104,000 records. I am building a package to check it and pull
out the most recent records and put them where they go in my SQL
Server 2000 tables. The only way I can think of to get the most
recent records is to use a global variable in the package to remember
the record count and then get those records from that record position
on. Problem is, I have no idea how I would go about selecting records
from a record position. Does anybody have any ideas or should I be
using a different approach? There are no time stamps to work from. I
was told that the AS400 records, including updates, are appended to
the table, which is why I thought this approach made sense. I would
truly appreciate any help.

[Simon Hayes]

Re: DTS Select using record position

phantomtoe@yaho o.com (Rowan) wrote in message news:<4bbf8d70. 0403031553.6e5f 7de7@posting.go ogle.com>...[color=blue]
> Hi there, it has been a while since i have posted. I am in a
> situation where I am stumped. I am learning to build a dts package
> where I am connecting to a table in an AS400. This database is being
> maintained by an outsourced company and therefore I can't change the
> table structure or even ask them to. Anyway, this table currently has
> about 104,000 records. I am building a package to check it and pull
> out the most recent records and put them where they go in my SQL
> Server 2000 tables. The only way I can think of to get the most
> recent records is to use a global variable in the package to remember
> the record count and then get those records from that record position
> on. Problem is, I have no idea how I would go about selecting records
> from a record position. Does anybody have any ideas or should I be
> using a different approach? There are no time stamps to work from. I
> was told that the AS400 records, including updates, are appended to
> the table, which is why I thought this approach made sense. I would
> truly appreciate any help.[/color]

How would you know where the recorded position is? Does the AS/400
have a row number field, or something similar? And what happens if
rows are deleted in the source table?

Assuming that the AS/400 table has a primary key, then you might be
able to create a linked server from your MSSQL server, and then use a
query like this to insert all the rows which don't exist:

insert into dbo.MyTable (col1, col2, ...)
select col1, col2, ...
from AS400...Table t
where not exists (select * from dbo.MyTable mt
where mt.PrimaryKey = t.PrimaryKey)

Updates would be more difficult, as you'd need to have some way of
identifying the rows which have been modified. You might want to
consider looking at heterogeneous replication for this, so that
changes on the AS/400 are automatically replicated to the SQL Server,
although you would probably need a third-party tool for this.

Simon