Specify an Interval of Change Data

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

In the control flow of an Integration Services package that performs an incremental load of change data, the first task is to calculate the endpoints of the change interval. These endpoints are datetime values and will be stored in package variables for use later in the package.

Note

For a description of the overall process of designing the control flow, see Change Data Capture (SSIS).

Set Up Package Variables for the Endpoints

Before configuring the Execute SQL task to calculate the endpoints, the package variables that will store the endpoints have to be defined.

To set up package variables

1. In SQL Server Data Tools (SSDT), open a new Integration Services project.

2. In the Variables window, create the following variables:

   1. Create a variable with the datetime data type to hold the starting point for the interval.

      This example uses the variable name, ExtractStartTime.

   2. Create another variable with the datetime data type to hold the ending point for the interval.

      This example uses the variable name, ExtractEndTime.

If you calculate the endpoints in a master package that executes multiple child packages, you can use Parent Package Variable configurations to pass the values of these variables to each child package. For more information, see Execute Package Task and Use the Values of Variables and Parameters in a Child Package.

Calculate a Starting Point and an Ending Point for Change Data

After you set up the package variables for the interval endpoints, you can calculate the actual values for those endpoints and map those values to the corresponding package variables. Because those endpoints are datetime values, you will have to use functions that can calculate or work with datetime values. Both the Integration Services expression language and Transact-SQL have functions that work with datetime values:

Functions in the Integration Services expression language that work with datetime values

• DATEADD (SSIS Expression)

• DATEDIFF (SSIS Expression)

• DATEPART (SSIS Expression)

• DAY (SSIS Expression)

• GETDATE (SSIS Expression)

• GETUTCDATE (SSIS Expression)

• MONTH (SSIS Expression)

• YEAR (SSIS Expression)

Functions in Transact-SQL that work with datetime values
Date and Time Data Types and Functions (Transact-SQL).

Before you use any one of these datetime functions to calculate the endpoints, you have to determine whether the interval is fixed and occurs on a regular schedule. Typically, you want to apply changes that have occurred in source tables to destination tables on a regular schedule. For example, you might want to apply those changes on an hourly, daily, or weekly basis.

After you understand whether your change interval is fixed or is more random, you can calculate the endpoints:

• Calculating the starting date and time. You use the ending date and time from the previous load as the current starting date and time. If you use a fixed interval for incremental loads, you can calculate this value by using the datetime functions of Transact-SQL or of the Integration Services expression language. Otherwise, you might have to persist the endpoints between executions, and use an Execute SQL task or a Script task to load the previous endpoint.

• Calculating the ending date and time. If you use a fixed interval for incremental loads, calculate the current ending date and time as an offset from the starting date and time. Again, you can calculate this value by using the datetime functions of Transact-SQL or of the Integration Services expression language.

In the following procedure, the change interval uses a fixed interval and assumes that the incremental load package is run daily without exception. Otherwise, change data for missed intervals would be lost. The starting point for the interval is midnight the day before yesterday, that is, between 24 and 48 hours ago. The ending point for the interval is midnight yesterday, that is, the previous night, between 0 and 24 hours ago.

To calculate the starting point and ending point for the capture interval

1. On the Control Flow tab of SSIS Designer, add an Execute SQL Task to the package.

2. Open the Execute SQL Task Editor, and on the General page of the editor, select the following options:

   1. For ResultSet, select Single row.

   2. Configure a valid connection to the source database.

   3. For SQLSourceType, select Direct input.

   4. For SQLStatement, enter the following SQL statement:

      SELECT DATEADD(dd,0, DATEDIFF(dd,0,GETDATE()-1)) AS ExtractStartTime,  
        DATEADD(dd,0, DATEDIFF(dd,0,GETDATE())) AS ExtractEndTime  
      
      

3. On the Result Set page of the Execute SQL Task Editor, map the ExtractStartTime result to the ExtractStartTime package variable, and map the ExtractEndTime result to the ExtractEndTime package variable.

   Note

   When you use an expression to set the value of an Integration Services variable, the expression is evaluated every time that the value of the variable is accessed.

Next Step

After you calculate the starting point and ending point for a range of changes, the next step is to determine whether the change data is ready.

Next topic: Determine Whether the Change Data Is Ready

See Also

Use Variables in Packages
Integration Services (SSIS) Expressions
Execute SQL Task
Script Task