Source MSSQL: verifyCursorColumnValues query doesn't run on Azure Synapse Analytics

[Alonkad]

Connector Name

source-mssql

Connector Version

1.1.1

What step the error happened?

During the sync

Revelant information

We have a connection set up to do daily incremental appends which works perfectly.
Now the DB is migrated to Azure Synapse Analytics.
While the MSSQL source connector declares it supports Synapse, we’ve encountered the following issue:

When incremental sync mode is used - the connector will run the following query to verify that the cursor column doesn’t contain NULL values:
SELECT CAST(IIF(EXISTS(SELECT TOP 1 1 FROM "%s"."%s" WHERE "%s" IS NULL), 1, 0) AS BIT) AS nullvalue
The problem is that regular MSSQL DB runs this query while on Synapse there’s a syntax error (as shown in the screenshots).

We can’t tell AirByte to avoid or skip this check and we can’t modify the query to be able to run on Synapse, for example:
SELECT CASE WHEN (SELECT TOP 1 1 FROM "%s"."%s" WHERE "%s" IS NULL)=1 then 1 else 0 end as nullvalue

Relevant log output

2023-09-19 12:17:07 source > INFO i.a.i.s.j.AbstractJdbcSource(logPreSyncDebugData):450 Data source product recognized as Microsoft SQL Server:12.00.2531
2023-09-19 12:17:07 source > INFO i.a.i.s.j.AbstractJdbcSource(discoverInternal):166 Internal schemas to exclude: [spt_fallback_db, spt_monitor, cdc, spt_values, INFORMATION_SCHEMA, spt_fallback_usg, MSreplication_options, sys, spt_fallback_dev]
2023-09-19 12:17:50 source > INFO i.a.i.s.m.MssqlSource(lambda$verifyCursorColumnValues$4):282 MsSQL Table Structure {"TABLE_QUALIFIER":"DbName","TABLE_OWNER":"SchemaName","TABLE_NAME":"Members","COLUMN_NAME":"birth_date","DATA_TYPE":12,"TYPE_NAME":"varchar","PRECISION":18,"LENGTH":18,"NULLABLE":1,"SQL_DATA_TYPE":12,"CHAR_OCTET_LENGTH":18,"ORDINAL_POSITION":1,"IS_NULLABLE":"YES","SS_DATA_TYPE":39}, SchemaName, Members
2023-09-19 12:17:51 source > ERROR i.a.i.b.s.SshWrappedSource(read):76 Exception occurred while getting the delegate read iterator, closing SSH tunnel com.microsoft.sqlserver.jdbc.SQLServerException: Parse error at line: 1, column: 18: Incorrect syntax near '('.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265) ~[mssql-jdbc-10.2.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1676) ~[mssql-jdbc-10.2.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:907) ~[mssql-jdbc-10.2.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:802) ~[mssql-jdbc-10.2.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7730) ~[mssql-jdbc-10.2.1.jre8.jar:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3786) ~[mssql-jdbc-10.2.1.jre8.jar:?]

Contribute

• Yes, I want to contribute

[rodireich]

Hi @Alonkad ,

One way I think you can work around this error is to configure your connection use CDC, which will override this cursor check.
If I can as you to please attach the full log.
Thanks

I reached out also on slack.

[Alonkad]

Hey @rodireich,

Thanks for looking into this issue.

Unfortunately CDC is not possible in our use-case for two reasons:
a. I'm not the owner of the source DWH, so I can't turn it on.

b. We're pulling data from views (prepared for us by the owner of the DWH), and I believe CDC doesn't work on views.

[rodireich]

The change you suggested seems trivial and is supported by all sql servers.
Unlike the IIF command which is not available on Azure Synapse Analytics