# tMSSqlCDC Extracts the changes made to the source operational data and makes them available to the target system(s) using database CDC views. tMSSqlCDC extracts source system data that has changed since the last extraction and transports it to another/other system(s). Information noteNote: If batch processing is enabled in the Job, the component cannot capture the order of changes and the source and target databases will not be synchronized. ## tMSSqlCDC Standard properties These properties are used to configure tMSSqlCDC running in the Standard Job framework. The Standard tMSSqlCDC component belongs to the Databases family. The component in this framework is available in all subscription-based Talend products. Information noteNote: This component is a specific version of a dynamic database connector. The properties related to database settings vary depending on your database type selection. For more information about dynamic database connectors, see [DB Generic components](https://help.qlik.com/talend/r/en-US/8.0/components/db-generic/db-generic-component). ## Basic settings | Properties | Description | | --- | --- | | Database | Select the desired database type from the list and click
Apply. | | Use an existing connection | Select this check box and in the Component
List drop-down list, select the desired connection component to
reuse the connection details you already defined.

Information noteNote: When a Job contains the parent Job and the child Job, do the following if you
want to share an existing connection between the parent Job and the child Job (for example,
to share the connection created by the parent Job with the child Job).

  1. In the parent level, register the database connection to be shared in the Basic settings view of the connection component which creates that very database connection.
  2. In the child level, use a dedicated connection component to read that registered database connection.


For an example about how to share a database connection across Job levels,
see [Sharing a database connection](https://help.qlik.com/talend/r/en-US/7.3/studio-user-guide/sharing-database-connection). | | Property type | Either Built-in or Repository.



Information noteWarning: Reset the DB type by clicking the
relevant button to select the CDC connection. | | JDBC Provider | Select the provider of the JDBC driver to be used, either Microsoft (the default, recommended) or Open
source JTDS.

When Microsoft is selected, you need to download the
Microsoft JDBC Driver for SQL Server on [Microsoft Download
Center](https://www.microsoft.com/en-us/download/details.aspx?id=11774), unpack the downloaded zip file, choose a jar in the
unzipped folder based on your JRE version, rename the jar to *mssql-jdbc.jar* and install it manually. For more
information about choosing the jar, see the System Requirements
information on [Microsoft Download
Center](https://www.microsoft.com/en-us/download/details.aspx?id=11774).

When using this component with Datasource in Talend Runtime, you need
to use the Open source JTDS driver. | | Host | Enter the IP address or the hostname of the database server or the Azure Synapse
Analytics to be used.

If the SQL Server Browser service is running on the
machine where the server resides, you can connect to a named instance through a
TCP dynamic port by providing the host name and the instance name in this field
in the format of {host\_name}\{instance\_name}. In this
case, you can leave the Port field empty. See [SQL Server Browser service](https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service?view=sql-server-ver16) for related
information. | | Port | Enter the listening port number of the database server or the Azure Synapse
Analytics to be used.

If the SQL Server Browser service is running on the
machine where the server resides, you can connect to a named instance through a
TCP dynamic port by providing the host name and the instance name in the
Host field and leave this field empty. See [SQL Server Browser service](https://docs.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service?view=sql-server-ver16) for related
information. | | Database | Enter the name of the database. | | Schema | Enter the name of the schema. | | Username and
Password | Enter the authentication data.

To enter the password, click the
[...] button next to the
Password field, enter the password in double
quotes in the pop-up dialog box, and then click
OK.

You can use
Type 2 integrated authentication on
Windows by adding
integratedSecurity=true in the
Additional JDBC Parameters field and leave
these two fields empty. See section *Connecting with integrated
authentication On Windows* at [Building the connection URL](https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?redirectedfrom=MSDN) for related
information. | | Schema using CDC and Edit Schema | A schema is a row description, it defines the number of fields to be
processed and passed on to the next component. The schema is either Built-in or stored remotely in the Repository.


Built-In: You create and store the schema locally for this component
only.


Repository: You have already created the schema and stored it in the
Repository. You can reuse it in various projects and Job designs.

Information noteWarning: Reset the DB type by clicking the
relevant button to select the schema of the CDC connection.

Click Edit
schema to make changes to the schema. If the current schema is of the Repository type, three options are available:

| | Table name | Select the source table from which changes made to data are to be captured. | | Subscriber | Enter the name of the application that will use the change table. | | Events to catch | | | Limit | Maximum number of consumed rows a subscriber can recover from the change
table, per execution. | ## Advanced settings | Properties | Description | | --- | --- | | Additional JDBC parameters | Specify additional connection properties for the DB connection you are
creating. This option is not available if you have selected the Use an existing connection checkbox in the
Basic settings. | | Authenticate using Azure Active
Directory | When selected, this option allows you to pick the authentication mode to use
from the Azure Active Directory authentication mode
dropdown list:



Read the Usage section of this document for
information about Microsoft SQL Server/JDBC encryption requirements.

This option is only available when
Microsoft is selected from the JDBC
Provider dropdown list in the Basic
settings view. | | Keep data in CDC Table | Select this checkbox to retain changes in the CDC table, allowing one or
more target systems to consult the data even after ingestion. When enabled, this
option prevents deletion of the consulted CDC data; instead, it updates the value
of the field CDC\_STATE to 'x'. This mechanism
lets users recheck previously ingested data by filtering records where
CDC\_STATE = 'x' (for example, using a
tMysqlInput component), and manage deletion themselves if needed. This option is
especially useful for users who need to audit or revisit ingested data without
having to re-run Jobs or reverse-engineer the process to understand how the
checkbox affects CDC\_STATE. | | Trim all the String/Char columns | Select this checkbox to delete any spaces found at the beginning or end of
fields, in all of the columns containing character strings. | | Trim column | Remove leading and trailing whitespace from defined columns.

Information noteNote: Clear Trim all the String/Char
columns to enable Trim columns in this field. | | tStatCatcher Statistics | Select this checkbox to collect log data at a component level. | | Enable parallel execution | Select this check box to perform high-speed data processing, by treating
multiple data flows simultaneously. Note that this feature depends on the database or
the application ability to handle multiple inserts in parallel as well as the number of
CPU affected. In the Number of parallel executions
field, either:



Note that when parallel execution is enabled, it is not possible to use global
variables to retrieve return values in a subJob.

| ## Global Variables | Variables | Description | | --- | --- | | Global Variables | NB\_LINE:
the number of rows processed. This is an After variable and it returns an integer.


ERROR\_MESSAGE: the error message generated by the
component when an error occurs. This is an After variable and it returns a string. This
variable functions only if the Die on error checkbox is
cleared, if the component has this checkbox.

A Flow variable functions during the execution of a component
while an After variable functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl+Space to access the variable list and choose the variable to use from
it.

For more information about variables, see [Using contexts and variables](https://help.qlik.com/talend/r/en-US/8.0/studio-user-guide/contexts-and-variables). | ## Usage | Usage guidance | Description | | --- | --- | | Usage rules | This component is used as a start component. It requires an output component
and row Main link.

Information noteImportant: Microsoft SQL Server/JDBC encryption
requirements

Starting with recent versions of the Microsoft SQL Server JDBC
driver, TLS encryption is enabled by default
(encrypt=true;trustServerCertificate=false). This
enhances security, but if your SQL Server does not support encrypted
connections or does not require clients to use TLS, you must set
encrypt=false in the JDBC parameters of this component
to connect successfully.



For more information on how to use connections with encryption
properties, see [Microsoft SQL JDBC documentation](https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ver17). |