Microsoft SQL Server CDC Connector

Back Next

Microsoft SQL Server CDC source properties in mappings

When you create a mapping, you configure a Source transformation for a Microsoft SQL Server CDC source and set some source properties for the Source transformation.

Click the Source transformation box on the mapping canvas. Then configure the source properties in the

Source Properties

panel below the mapping canvas.

If you have multiple Source transformations in a mapping, configure source properties for each source. If you use a specific connection instead of a connection parameter, enter the same connection for all sources.

If a mapping includes source tables or columns that have special characters in their names, the associated mapping task fails because it cannot import the source metadata. Special characters include s #, $, @, %, *, !, and ~. Therefore, ensure that none of the source tables and mapped columns have names with any special character. Rename the tables and columns if necessary.

The

Source Properties

panel contains the following tabs on which you can enter information:

General

tab. Specify a user-defined name and optional description for the SQL Server CDC source.

Source

tab. Configure the source properties under

Details

and under

Advanced

. The advanced properties are available only if you enter a specific CDC connection for the source. If you enter a parameter for the source connection, the advanced properties are not displayed. However, you can define these properties in the associated mapping task after you select a specific source connection for the task.

The Filter and Sort options under

Query Options

are not supported for CDC connections.

The following table describes the SQL Server CDC source properties under

Details

Property	Description
Connection	Required . Select a source connection of the type SQL Server CDC , or click New Connection to create one. Alternatively, you can define a connection parameter in the mapping and enter a specific connection in each mapping task that is associated with the mapping. If you want to use a connection parameter with a specific source object, you must first select a specific connection so that you can access the source to select the source object in the Object field. After you select the source object, define the connection parameter. If you want to use parameters for both the connection and source object, you do not need to first select a specific connection. You can just define the parameters in either order. To define a connection parameter, click New Parameter next to the Connection property and enter the following information: Enter a parameter name that begins with a letter and is up to 200 characters in length, including @, #, _, and alphanumeric characters. In the Type property, verify that connection is selected. Leave the Connection Type property blank. After you click OK , the parameter appears in the Connection property.
Source Type	Required . Select Single Object to be able to select an extraction map for a SQL Server source table in the Object property. Alternatively, you can define a parameter for the source object in the mapping and enter a specific source object in each mapping task that is associated with the mapping. To define a parameter, select Parameter as the source type, and then specify the parameter in the Parameter property.
Object	Click Select . In the Select Source Object dialog box, under Packages , click a listed value. This value is the first part of the generated extraction map name. The right pane lists extraction maps that have names matching the selected package value. Select an extraction map. You can use only extraction maps that PowerExchange generated by default for capture registrations. These extraction maps have names that begin with "d." The extraction-map metadata is stored in the Informatica Intelligent Cloud Services repository in the cloud. The Object property appears only if you select Single Object as the source type. The Preview Data feature is not supported for CDC source objects.
Parameter	Select a parameter for the source object, or click New Parameter to define a new parameter for the source object. The Parameter property appears only if you select Parameter as the source type.

The following table describes the optional source properties under

Advanced

Advanced Property	Description
Connection Overrides	A variable or parameter file that overrides attributes at the mapping level for one or more sources. Use a semicolon (;) as the separator. For example, to override attributes by using a parameter file, use the following syntax: RestartToken FileName=D:\abcagent\apps\Data_Integration_Server\data\restart\test.dat;APPLICATION=testapp Overrides can be parameterized and can refer to other parameters. For example: RestartToken File Name=$PMRootDir/restart/restartfile.txt If you have more than one CDC source in the mapping, you only need to define the override in one of the sources. The override is automatically applied to all sources. If you provide an override value for more than one source, all values must match to avoid a runtime error. For more information, see Connection overrides reference.
Map Schema Override	A schema name that overrides the schema name in the PowerExchange data map.
Map Name Override	A map name that overrides the name defined in the PowerExchange data map.
Filter Overrides	One or more conditions that filter the source change records that the PWX CDC Reader retrieves from the PowerExchange Logger log files. Based on the filter conditions, a WHERE clause is appended to the default SQL SELECT query that the PWX CDC Reader uses to read the change records. You must use the nonrelational SQL syntax that PowerExchange supports for comparison and LIKE operators. For more information, see the "PowerExchange Nonrelational SQL" chapter in the PowerExchange Reference Manual . If the filter includes a date or timestamp column, ensure that the range of years in the column data is within the range of years for PowerExchange data filtering, as controlled by the DATERANGE statement in the DBMOVER configuration file. The default range is 1800 to 2200. For example, to select change records in which the TYPE column has a value of A or D, specify the following condition: TYPE='A' or TYPE='D' Maximum length of the entire SELECT statement that PowerExchange builds, including the WHERE clause with the filter conditions, is 8192 bytes. If you specify the Filter Overrides attribute and also specify an SQL Query Override attribute value that contains a filtering WHERE clause, the resulting SELECT statement contains a WHERE clause that uses the AND operator to associate the Filter Overrides filter conditions with the SQL Query Override conditions. For example: SELECT * from `schema`.`table` WHERE `Filter_Overrides_conditions` AND `SQL_Query_Override_conditions`
SQL Query Override	An SQL statement that overrides the default SQL query that the PWX CDC Reader uses to retrieve change records from PowerExchange. You must use the nonrelational SQL syntax that PowerExchange supports. For more information, see the "PowerExchange Nonrelational SQL" chapter in the PowerExchange Reference Manual . If the override statement includes a date or timestamp column, ensure that the range of years in the column data is within the range of years for PowerExchange data checking and filtering, as controlled by the DATERANGE statement in the DBMOVER configuration file. The default range is 1800 to 2200. For example, to select change records for the USER source table when the TYPE column has the value of A or D, specify the following query: SELECT ID, NAME from `schema`.`extract_map`.USER where TYPE=‘A’ or TYPE=‘D’; Maximum length of the SELECT query override statement is 8192 bytes. If you specify an SQL Query Override attribute value that contains a filtering WHERE clause and also specify the Filter Overrides attribute, the resulting SELECT statement contains a WHERE clause that uses the AND operator to associate the SQL Query Override conditions with the Filter Overrides filter conditions. For example: SELECT * from `schema`.`table` WHERE `Filter_Overrides_conditions` AND `SQL_Query_Override_conditions`
Tracing Level	The level of detail that appears in the session log for a Source transformation. Select one of the following options, which are listed in order of increasing detail: Terse Normal Verbose Initialization Verbose Data Default is Normal .