Getting new and updated data with an archive column

Question

I am trying to optimize an SSIS package that retrieves data from a very large transaction table that gets millions of new records daily. The data is being collected from several hundred field servers where SQL Server 2008 Standard Edition is installed. CDC, therefore cannot be used as it is an Enterprise feature. The plan is this:

1. Archive column, smallint - 0=new (default constraint), 1=archived, 2=updated (trigger)
2. Default constraint Archive=0
3. Update trigger Archive=2
4. Index on archive, timestamp

The SSIS package will then get all records where Archive=0, insert those into the destination then get all records where Archive=2 and update them in the destination.

What I need help with is the setting of the Archive column to 1 after the SSIS procedure is done writing new or updated rows. What would be the most efficient way of setting all of the rows processed without touching any rows that might have been inserted or updated after the initial select?

Answer 1

How about making use of the OUTPUT virtual table? Set your transaction isolation level correctly (snapshot/serializable) so that you only see the rows as of the moment your process begins.

Use the following for your OLE DB Source

UPDATE T SET Archive = 1 OUTPUT DELETED.* FROM Table T WHERE T.Archive = 0;

That updates everything in a nice atomic operation with a side effect of generating the target output into your data flow buffers. Route that to your destination and it's done. Nice and neat

Answer 2

Since you are using SSIS and capturing the changed rows, you should have SSIS capture the PK values for the rows that are being processed. The last step of the package should be to do a targeted update on the source table, setting the [Archive] field to 1 based on those PK values.

And if not already done, the update trigger needs to exclude those changes from setting the [Archive] field to 2. You will probably need something along the lines of:

UPDATE tab
SET    tab.[Archive] = 2
FROM   dbo.MyTable tab
INNER JOIN INSERTED ...
INNER JOIN DELETED ...
WHERE INSERTED.Archive = DELETED.Archive
AND   DELETED.Archive IN (0, 1) -- Add 3 to this list; see below for why

BUT, then you need to deal with the scenario where a row gets update during the ETL process. You still want to ETL that change the next time around. But if the ETL process is running it will change the [Archive] field to 1 at the end and then there is no record of that row being changed. So then you kinda need another value to indicate "Being ETLed", maybe 3. You can then, similar to what @billinkc suggested in a comment on the question, do an UPDATE with the OUTPUT clause as follows:

UPDATE TOP (@BatchSize) tab
SET    tab.[Archive] = 3
OUTPUT INSERTED.*
FROM   dbo.MyTable tab
WHERE  tab.[Archive] IN (0, 2)
ORDER BY tab.[Archive] ASC;

Then, in the final step of the SSIS package, the targeted update should be on both the PK values AND [Archive] = 3. And add 3 to the IN list in the trigger update query above. This way, only records that have not changed during the ETL process will get updated to 1, while records that have been changed during the ETL will appear as 2 and will get picked up again the next time around with their modified values.

Of course, you might also want to create a filtered index on the [Archive] field to support the getting of records marked as 0, 2, or 3. Hence:

CREATE INDEX ... WHERE [Archive] IN (0, 2, 3);

You want to grab the records marked as 3 because, assuming this is single-threaded per field server, records marked as 3 represent stuck records due to a prior process that failed. This is also why you don't want to mark the records as 1 in the UPDATE...OUTPUT when you initially get them; if the process fails then they will be marked as already archived and not picked up again.

---

That being said, you might be better off using a queue table instead of a status field. You could create a simple table with just a few fields:

QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTablePKfield  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

Then the trigger becomes a simple INSERT, UPDATE trigger that just does:

INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.PKfield
  FROM   INSERTED ins;

Then the SSIS process gets the values via:

SELECT TOP (@BatchSize) etl.QueueID, tab.field1, tab.field2...
FROM   dbo.MyTableEtlQueue etl
INNER JOIN dbo.MyTable tab
        ON tab.PKfield = etl.MyTablePKfield
ORDER BY etl.QueueID ASC;

And the end of the SSIS process does a simple delete against the [MyTableEtlQueue] table based on the [QueueID] values it pulled in at the beginning.

In this model, you don't need the filtered index, or status column on a large table, which reduces contention on, and size of, that already large table. You don't need to worry about records in transit, whether in terms of updates or process failures.

EDIT:
Given the new information of the source table not having any uniqueness, that requires only a minor change to this queue table method: instead of tracking only the PKfield (which apparently doesn't exist ;-), track all of the fields (or at least all that need to be ETLed).

So the queue table changes to be:

QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTableField1  INT NOT NULL,
MyTableField2  INT NOT NULL,
...
MyTableFieldN  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

And the trigger changes to be:

INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.MyTableField1, ins.MyTableField2, ..., ins.MyTableField1N
  FROM   INSERTED ins;

Then the SSIS process changes to be (note the lack of JOIN to the source table now):

SELECT TOP (@BatchSize) etl.QueueID, etl.MyTableField1, etl.MyTableField2, ...
FROM   dbo.MyTableEtlQueue etl
ORDER BY etl.QueueID ASC;

This queue table method isolates the source table (and its processing) from the audit/ETL process (much like CDC does). This allows you app to interact with the source table with no issues, and no new column which takes up more space on the datapage, and no potentially long running transaction holding onto the source table while the ETL processes.

Answer 3

Change Tracking (CT) and Change Data Capture (CDC) are built into SQL Server. CT can identify the rows which changed since you last asked. CDC can provide a complete history of all data changes since you last asked. Both are easily manipulated from SSIS.

There will be no need to reset flags per row after processing. The system handles all this through additional, hidden tables specifically for this purpose.

Answer 4

A pure SSIS solution would be to deliver the Data Flows for the Insert and Update tasks into Cache Transformations.

http://msdn.microsoft.com/en-us/library/bb895264.aspx

If they occur in 1 Data Flow then you might be able to send them to a single Cache, otherwise you will need 2.

Then you can test against those Caches in later Data Flows using the Lookup transformation. I would redirect any Matched rows to effectively discard them, then continue with just the unmatched rows.

Note Caches are stored in memory so try to keep the content lean e.g. just the key column(s). As long as you dont exhaust Physical Memory, the runtime performance will be very fast (no I/O).

This solution is probably the simplest to build and test, as it needs no code to implement.