By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,742 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,742 IT Pros & Developers. It's quick & easy.

DTS Select using record position

P: n/a
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.
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ph********@yahoo.com (Rowan) wrote in message news:<4b**************************@posting.google. com>...
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.


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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.