Error: CDC LSN Cursor Is Expected to Be Set in SqlServerImportTask

Issue

The following error appears in Fivetran:

java.lang.IllegalStateException: CDC LSN cursor is expected to be set in SqlServerImportTask

Environment

Connector: SQL Server

Resolution

To resolve this error, do the following:

1. Run the following query to check the status of the SQL Server Agent:

   IF EXISTS(SELECT 1 FROM MASTER.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
   BEGIN
    SELECT SERVERPROPERTY('ServerName') AS 'InstanceName', 'Running' AS 'SQLServerAgent Status'
   END
   ELSE
   BEGIN
     SELECT SERVERPROPERTY('ServerName') AS 'InstanceName', 'Stopped' AS 'SQLServerAgent Status'
     END 
   

   If the agent is stopped, start it and check if the issue is resolved.
2. Ensure change data capture (CDC) is enabled for the database and relevant tables using the following query:

   EXEC sys.sp_cdc_help_change_data_capture
   

3. Verify that the Fivetran user has the necessary permissions to run CDC-related functions. The user must have execute permission on sys.fn_cdc_get_max_lsn and be a member of the public role.
4. Check if the CDC capture and cleanup jobs exist and are running using the following query:

   SELECT * FROM msdb.dbo.cdc_jobs;
   

5. If the issue persists, disable and re-enable CDC on the affected tables.

Cause

This issue occurs when the CDC process in SQL Server has been configured or initialized incorrectly. The error indicates that the Log Sequence Number (LSN) cursor is missing or set incorrectly, possibly due to misconfiguration or missing prerequisites.