Determining Whether the Change Data Is Ready

On the Control Flow tab of the SSIS Designer, add a For Loop container to the control flow.

Connect the Execute SQL Task that calculates the endpoints of the interval to the For Loop container.

In the For Loop Editor, select the following options:

1. For InitExpression, enter @DataReady = 0.

   This expression sets the initial value of the loop variable.

2. For EvalExpressionm, enter @DataReady == 0.

   When this expression evaluates to False, execution passes out of the loop and the incremental load starts.

Inside the For Loop container, add an Execute SQL task.

In the Execute SQL Task Editor, on the General page, 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:

   declare @DataReady int, @TimeoutCount int
   
   if not exists (select tran_end_time from cdc.lsn_time_mapping
           where tran_end_time > ?  )
       select @DataReady = 0
   else
       if ? = 0
           select @DataReady = 3 
   else
       if not exists (select tran_end_time from cdc.lsn_time_mapping
               where tran_end_time <= ? )
           select @DataReady = 1 
   else
       select @DataReady = 2
   
   select @TimeoutCount = ?
   if (@DataReady = 0)
       select @TimeoutCount = @TimeoutCount + 1
   else
       select @TimeoutCount = 0
   
   if (@TimeoutCount > ?)
       select @DataReady = 5
   
   select @DataReady as DataReady, @TimeoutCount as TimeoutCount
   

On the Parameter Mapping page of the Execute SQL Task Editor, make the following mappings:

1. Map the ExtractEndTime variable to parameter 0.

2. Map the IntervalID variable to parameter 1.

3. Map the ExtractStartTime variable to parameter 2.

4. Map the TimeoutCount variable to parameter 3.

5. Map the TimeoutCeiling variable to parameter 4.

On the Result Set page of the Execute SQL Task Editor, map the DataReady result to the DataReady variable, and the TimeoutCount result to the TimeoutCount variable.

Inside the For Loop container, add a Script task.

Connect the Execute SQL task that queries to determine whether the change data is ready to the new Script task.

For the precedence constraint that connects the Execute SQL task to the Script task, open the Precedence Constraint Editor and select the following options:

1. For Evaluation operation, select Expression and Constraint.

2. For Value, select Success.

   The constraint value of Success refers to the success of the previous task. In this case, the success of the Execute SQL task.

3. For Expression, enter @DataReady == 0 && @TimeoutCount <= @TimeoutCeiling.

4. Select Logical AND. All constraints must evaluate to True, if not already selected.

In the Script Task Editor, on the Script page, for ReadOnlyVariables, select the User::DelaySeconds integer variable from the list.

In the Script Task Editor, on the Script page, click Edit Script to open the script development environment.

In the Main procedure, enter one of the following lines of code:

• If you are programming in C#, enter the following line of code:

  System.Threading.Thread.Sleep((int)Dts.Variables["DelaySeconds"].Value * 1000);
  

  - or -

• If you are programming in Visual Basic, enter the following line of code:

  System.Threading.Thread.Sleep(Ctype(Dts.Variables("DelaySeconds").Value, Integer) * 1000)
  

  Note

  The Thread.Sleep method expects an argument that is specified in milliseconds.

Leave the default line of code which returns DtsExecResult.Success from the execution of the script.

Close the script development environment and the Script Task Editor.

Inside the For Loop container, add an Execute SQL task.

Connect the Execute SQL task that queries to determine whether the change data is ready to the new Execute SQL task.

For the precedence constraint that connects the two Execute SQL tasks, open the Precedence Constraint Editor and select the following options:

1. For Evaluation operation, select Expression and Constraint.

2. For Value, select Success.

   The constraint value of Success refers to the success of the previous Execute SQL task.

3. For Expression, enter @DataReady == 0.

4. Select Logical AND. All constraints must evaluate to True, if not already selected.

   This selection requires that both conditions, the constraint and the expression, must be true.

In the Execute SQL Task Editor, on the General page, 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:

   WAITFOR DELAY ?
   

On the Parameter Mapping page of the editor, map the DelaySeconds string variable to parameter 0.

If you want to report the error or timeout by writing a message to the log, configure logging for the package. For more information, see How to: Enable Logging in a Package.

Inside the For Loop container, add a Script task.

Connect the Execute SQL task that queries to determine whether the change data is ready to the new Script task.

For the precedence constraint that connects the Execute SQL task to the Script task, open the Precedence Constraint Editor and select the following options:

1. For Evaluation operation, select Expression and Constraint.

2. For Value, select Success.

   The constraint value of Success refers to the success of the previous task. In this case, the success of the Execute SQL task.

3. For Expression, enter @DataReady == 1 || @DataReady == 5.

4. Select Logical AND. All constraints must evaluate to True, if not already selected.

   This selection requires that both conditions, the constraint and the expression, must be true.

In the Script Task Editor, on the Script page of the editor, for ReadOnlyVariables, select User::DataReady and User::ExtractStartTime from the list to make their values available to the script.

If you want to include information from certain system variables (for example, System::PackageName) in the information that you write to the log, select those variables also.

In the Script Task Editor, on the Script page, click Edit Script to open the script development environment.

In the Main procedure, enter code to log an error by calling the Dts.Log method, or to raise an event by calling one of the methods of the Dts.Events interface. Inform the package of the error by returning Dts.TaskResult = Dts.Results.Failure.

The following sample shows how to write a message to the log. For more information, see Logging in the Script Task, Raising Events in the Script Task, and Returning Results from the Script Task.

    ' User variables.
    Dim dataReady As Integer = _
      CType(Dts.Variables("DataReady").Value, Integer)
    Dim extractStartTime As Date = _
      CType(Dts.Variables("ExtractStartTime").Value, DateTime)

    ' System variables.
    Dim packageName As String = _
      Dts.Variables("PackageName").Value.ToString()
    Dim executionStartTime As Date = _
      CType(Dts.Variables("StartTime").Value, DateTime)

    Dim eventMessage As New System.Text.StringBuilder()

    If dataReady = 1 OrElse dataReady = 5 Then

      If dataReady = 1 Then
        eventMessage.AppendLine("Start Time Error")
      Else
        eventMessage.AppendLine("Timeout Error")
      End If

      With eventMessage
        .Append("The package ")
        .Append(packageName)
        .Append(" started at ")
        .Append(executionStartTime.ToString())
        .Append(" and ended at ")
        .AppendLine(DateTime.Now().ToString())
        If dataReady = 1 Then
          .Append("The specified ExtractStartTime was ")
          .AppendLine(extractStartTime.ToString())
        End If
      End With

      System.Windows.Forms.MessageBox.Show(eventMessage.ToString())

      Dts.Log(eventMessage.ToString(), 0, Nothing)

      Dts.TaskResult = Dts.Results.Failure

    Else

      Dts.TaskResult = Dts.Results.Success

    End If

Close the script development environment and the Script Task Editor.