I am trying to refresh data in a table from one SQL database to the other SQL
database weekly. Each database is on its own server. The table on the
database on each server is identifcal. The key in the table is an identity
key.

Here is what I need help with. Since I am refreshing the data weekly, my
thinking is after the initial data load from the source to the destination
table, when the next refresh occurs in my DTS package, if I can pass the last
identify key value from the destination table connection to the query in the
'Source' tab in the Transform Data task properties, then the DTS will only
refresh the records that are newly inserted since last refresh.

Currently I am just manually put in the values in the following query in the
'Source' tab of the Transform Data Task Properties.

Select * from primarydata where sampleid between 1000 and 2000

Sample id 1000 is the last identity key value +1 in the destination table
and I manually do a query first to find out the number

Sample id 2000 is the max identity key value in the source table and I also
manually do a query to find out the number.

I like to automate the two manually query parts and I assume I have to have
some variables to store them. In the Transform Data task properties, under
the 'Source' tab, there is a 'Paramters' button. The 'Lookups' tab seems to
indicate it can do something with what I want to do. However, I am not
familar with them and need some pointers. If these two areas are not the
ones I should use, can someone give me some ideas of what I should do to
automate the above. If VB scripts are the one I should use, please give me
some samples and how to incorporate them in my DTS package. My VB knowledge
is quite limited so please give me some instructions.

Your help is very much appreciated.

Wingman

OK so the aim is to not make this a full refresh.

The problem with doing the last key value inserted ident is that what about
updates to existing rows or deletions?

You can approach this in a number of ways.

1. On the source have a ChangesAudit table. This will log through triggers
what happened between times A and B on the source server. You can then move
that to the destination server and use TSQL to do your UPDATES INSERTS and
DELETES.

2. Use a Linked Server. You can then simply use the key values on each
table , do the comparisons and perform whatever operations you need.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Wingman" <Wingman (AT) discussions (DOT) microsoft.com> wrote

Thanks for the response.

The table I am working on is Insert only and there is no modification done
in the table. I think I understand your ideas. But how do I incorporate
into DTS?
Assuming we are still using DTS to do this, either options in your email (1
or 2),I think I still need to have some kind of variables to store the keys
to pass from the destination to the source connection in DTS, right? Keep
in mind that I am doing this in a DTS package. it has a source connection,
a destination connection and a Transform Data Task in between the two
connections. The 'select' query (listed in my original email) is in the
'Source' tab of the Transform Data Task properties. The two numbers in the
select query need to become two variables and I need to know how to create
the variables and pass value to them in DTS.

"Allan Mitchell" wrote:

You can do this in DTS.

1. Linked server


SourceSQLStatement would look similar to

SELECT
ls.col1,
ls. <etc>
FROM server.db.owner.table ls LEFT OUTER JOIN LocalOwner.LocalTable loc
ON ls.PK = loc.PK
WHERE loc.PK IS NULL


This will give you the values that you can send over just like any other
statement.


2. Audit table (On Source)

Would look something like

Owner.MyAuditTable


Your SourceSQLStatement would look similar to

SELECT

<cols>

FROM SourceTable
WHERE KeyCol IN(SELECT KeyCol from Owner.MyAuditTable)



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Wingman" <Wingman (AT) discussions (DOT) microsoft.com> wrote