hi,

i was wondering if there's a way to use the Transform task (or another
existing DTS task) to transform/copy data from one (or more) table into
another table in an incremental fashion. that is, only transform data
that has not already been transformed. this would be used in a package
that gets executed at regular intervals while new data keeps getting
imported. does the Transform task, or any other task, keep track of a
"high water mark" or anything like that?

what's the best practice/recommendation for performing this kind of a
transform (either using an existing task, if available, or approaches
for coding a custom one)?

thanks!

-wg

Your best bet IMHO for this is Transactional Replication.

To do this in DTS you would need to identify rows that have changed/Been
inserted/deleted. A typical way to do this might be to add truggers to the
base tables and then log changes to an audit table. You then use the audit
table as a source for the DataPump. I would personally then use the Data
Driven Query task which allows you to run up to 4 statements per row of the
source. Note though that it can be slow if you have a lot of data.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"wg" <wg (AT) nospam-voiceme (DOT) org> wrote