Introduction:
I was recently challenged with creating a reporting database off of a lot of source tables that had no timestamp associated to any of the records. In order to allow for historical analysis of the data, the solution was to create an SSIS package that took a snapshot of the data each quarter. However, since the data in the underlying tables could change more frequently than once a quarter it was important to run the package every day so that reports would be up to date.
This was done using the following logic.
The Solution:
When the package was first kicked off a SQL Task is used to find the current quarter of the execution date of the SSIS job. For example, if the package ran on March 26, 2009, the quarter returned would be 200901. If the package ran on April 2, 2009, the quarter returned would be 200902.
The package stores the quarter in a variable and uses it to delete the current quarter from all of the database tables. The current quarter is then reloaded in all the tables, maintaining the history for all quarters previous to the value used by the variable, successfully updating only the current quarter of data and leaving the history intact.
However, since most of the tables use a surrogate key identity field, each time the data was deleted a large gap would be created between the identity of the first key used from the previous load, and the first key used when the data was reinserted.
For example, assume that a table has 1,000 rows. Each row is assigned a unique identity from 1 to 1,000. The last 250 rows belong to the current quarter. The package runs and deletes the last 250 rows. Since deleting records in a table does not reset the identity used in the surrogate key column, when the 250 rows are reloaded then the first key used is 1,001. This means that there is a gap between 750 and 1,001. Since the package runs each day, this gap can become quite large, i.e. a gap between 750 and 8,250.
This is not a big deal, however, in a table that has 40,000 rows, this can cause the surrogate key (identity) to grow exponentially.
In order to solve this issue, after the records are deleted from the table, a simple SQL query can be used to reset the identity of the table to the maximum value that is left remaining in the table. The SQL query is:
Declare @maxvalue int
Select @maxvalue = ISNULL(Max(Id),0) From dim_ExampleTable
DBCC CHECKIDENT
(
dim_ExampleTable, Reseed, @maxvalue
)
The identity for the table "dim_ExampleTable" has now been reset to a value equal to the maximum value that contains data within the table. In our example with 1,000 records when the data is deleted and reloaded, the next identity used will be 751, not 1,001.
