Getting new and updated data with an archive column 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:


Archive column, smallint - 0=new (default constraint), 1=archived, 2=updated (trigger)
Default constraint Archive=0
Update trigger Archive=2
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?
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.