We have a table on a legacy DB that stores change records. This table gets
populated in real time, everytime a change is made to any one of the hundred
of tables. We use these change records to update our records in "real time"
in SQLServer tables. I want to know if I can use DTS to 1) select all
existing records in the legacy change table 2) MOVE them (not copy, i.e. once
they are moved, they are deleted WITHOUT deleting any new records that may be
inserted as the DTS process is occurring) to a table in SQLServer 3) repeat
every 5-10 seconds so that we get updated records in a reasonable amount of
time, but never get the same record twice. We currently fulfill this
function by using *gasp* VFP programs that update a status field from 'C' to
'I' in the legacy table, selects all records with status = 'I', inserts those
records into a holding tank on SQLServer, then deletes records with status =
'I'. This process runs every 10 seconds. My hope is that by creating this
as a DTS package, that the process would run faster and reduce load on the
SQLServer, as our data throughput is about to increase substantially.

You could set the same thing up in a stored procedure that you could put
into a DTS and schedule to run.


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

I think I forgot to mention that the source data resides in a DB2 DB on
IBM... the destination of course is SQLServer... how would I go about
connecting to the DB2 DB in a sproc?

"Rick Gittins" wrote:

A move is always just a copy, followed by a delete, so the only trick
here is figuring out which rows you pulled over with the copy that you
can delete afterward. The easiest approach is probably to do it just
like you're doing it now using the status field. You can do the update
and delete steps using an ODBC connector.

The problem you may hit here is that DTS is designed for large amounts
of data once, rather than attempting to replicate. I don't think the
performance you're looking for can be acheived with straight DTS
through an ODBC connection. I'm looking at a very similar situation
(large number of tables migrating from old to new data schemas and the
need to keep both synced), but at least in mine, both sides are
SQLServer. What we're seeing is that trying to run more frequently
than every 2 or 3 minutes is almost not worth it. The overhead
obtaining the connections seems to be the problem.

An ideal solution would be to create a C# or C++ app that keeps the
source connection open, constantly polling it for new data. In any
case, good luck.

In this case where both sides are SQL Server would replication not be the
solution?

--



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


<rmheim (AT) gmail (DOT) com> wrote

Not when the table schemas are quite different.