Date Range Extract Pattern

The date range pattern is used to extract a subset of the source data based on a date range. The end of the date range is the current batch execution date. The start of the date range is a number of days prior to the current batch effective date. Each time the ETL runs, the current batch effective date, for that batch execution, is set to midnight of the prior day. The date range ‘window’ moves forward a day as the current batch effective date changes.

To use the date range pattern select ‘Date Range’ in the Extract dialog ‘Extract Pattern’ field.

The number of days is specified by a custom meta data tag called ‘DIL_Extract_Period_Days’ added to the tags table on the advanced tab of the Extract dialog. Set the value of this tag to the number of days. In the example below, the start date of the date range is 21 days prior to the current batch effective.

You must also tag one source column as the Date attribute that contains the date that is used to filter the data in the source based on the date range.  To identify this source column, edit it through the Mapping tab of the Extract dialog and add a tag named ‘DIL_Date_Attribute’. Set the value to ‘DIL_Date_Attribute’ also. The column must be of a date type that can be CAST to a datetime.

How does the Pattern works ?

When Dimodelo Data Warehouse Studio (DA) generates the source query in the SSIS Extract package, it generates it like this:

SELECT [TaskId]
,[TaskName]
,[TaskDescription]
,[TaskEstimateMinutes]
,[TaskTypeCode]
,[ServiceId]
,[ScheduledDate]
,NULL AS [Reject_Ind]
FROM
[dbo].[Task] with (noLock) WHERE CONVERT(datetime,[ScheduledDate],112) >= ?

Where ScheduledDate is the selected DIL_Date_Attribute.

Then the package calculates the first date of the range as:

DATEADD(“dd”, -21, @[User::Current_Batch_Effective_Date] )

Where 21 is the DIL_Extract_Period_Days. So for example, if the current effective date was  2017-01-21, then the resulting date would be 2016-12-31.

DA then substitutes the resulting date for the question mark in the query.

All data where the scheduledDate >= 2016-12-31 is extracted.