CDC Source

  • Article

The CDC source reads a range of change data from SQL Server 2012 change tables and delivers the changes downstream to other SSIS components.

The range of change data read by the CDC source is called the CDC Processing Range and is determine by the CDC Control task that is executed before the current data flow starts. The CDC Processing Range is derived from the value of a package variable that maintains the state of the CDC processing for a group of tables.

The CDC source extracts data from a SQL Server database by using a database table, a view, or an SQL statement.

The CDC source uses the following configurations:

  • A SQL Server ADO.NET connection manager to access the SQL Server CDC database. For more information about configuring the CDC source connection, see CDC Source Editor (Connection Manager Page).

  • A table enabled for CDC.

  • The name of the capture instance of the selected table (if more-than-one exists).

  • The change processing mode.

  • The name of the CDC state package variable based on which the CDC Processing range is determined. The CDC source does not modify that variable.

The data returned by the CDC Source is the same as that returned by the SQL Server CDC functions cdc.fn_cdc_get_all_changes_<capture-instance-name> or cdc.fn_cdc_get_net_changes_<capture-instance-name> (when available). The only optional addition is the column, __$initial_processing that indicates whether the current processing range can overlap with an initial load of the table. For more information about initial processing, see CDC Control Task.

The CDC source has one regular output and one error output.

Error Handling

The CDC source has an error output. The component error output includes the following output columns:

  • Error Code: The value is always -1.

  • Error Column: The source column causing the error (for conversion errors).

  • Error Row Columns: The record data that causes the error.

Depending on the error behavior setting, the CDC source supports returning errors (data conversion, truncation) that occur during the extraction process in the error output. For more information, see CDC Source Editor (Error Output Page).

Data Type Support

The CDC source component for Microsoft supports all SQL Server data types, which are mapped to the correct SSIS data types.

Troubleshooting the CDC Source

The following contains information on troubleshooting the CDC source.

Use this script to isolate problems and reproduce them in SQL Server Management Studio

The CDC source operation is governed by the operation of the CDC Control task executed before invoking the CDC source. The CDC Control task prepares the value of the CDC state package variable to contain the start and end LSNs. It performs function equivalent to the following script:

use <cdc-enabled-database-name>
               declare @start_lsn binary(10), @end_lsn binary(10)
               set @start_lsn = sys.fn_cdc_increment_lsn(
                       convert(binary(10),'0x' + '<value-from-state-cs>', 1))
               set @end_lsn = 
                       convert(binary(10),'0x' + '<value-from-state-ce>', 1)
               select * from cdc.fn_cdc_get_net_changes_dbo_Table1(@start_lsn,
@end_lsn, '<mode>')

where:

  • <cdc-enabled-database-name> is the name of the SQL Server database containing the change tables.

  • <value-from-state-cs> is the value that appears in the CDC state variable as CS/<value-from-state-cs>/ (CS stands for Current-processing-range-Start).

  • <value-from-state-ce> is the value that appears in the CDC state variable as CE/<value-from-state-cs>/ (CE stands for Current-processing-range-End).

  • <mode> are the CDC processing modes. The processing modes have one of the following values All, All with Old Values, Net, Net with Update Mask, Net with Merge.

This script helps isolate problems by reproducing them in the SQL Server Management Studio, where it is easy to reproduce and identify errors.

SQL Server Error Message

The following message may be returned by SQL Server:

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_<..>.

This error does not indicate that an argument is missing. It means that the start or end LSN values in the CDC state variable are invalid.

Configuring the CDC Source

You can configure the CDC source programmatically or through the SSIS Designer.

For more information, see one of the following topics:

The Advanced Editor dialog box contains the properties that can be set programmatically.

To open the Advanced Editor dialog box:

  • In the Data Flow screen of your SQL Server 2012 Integration Services (SSIS) project, right click the CDC source and select Show Advanced Editor.

For more information about the properties that you can set in the Advanced Editor dialog box, see CDC Source Custom Properties.

In This Section

Related Content