search

BuzzCutNorman / tap-mssql

Singer Tap for MS SQL built with Meltano Singer SDK.
MIT License
2 stars 9 forks source link

When `start_date` set in meltano.yml and a non datetime Replication Key is set `start_date` is used in where clause #47

Open BuzzCutNorman opened 1 year ago

BuzzCutNorman commented 1 year ago

If you setup Incremental replication on a stream select a non datetime column as a Replcation Key if you have a start_date set in the meltano.yml then during the initial incremental run it will fail with a pyodbc conversion error.

      start_date: '2022-01-03'
      raw-tags:
        replication-method: INCREMENTAL
        replication-key: Id
sqlalchemy.exc.DataError: (pyodbc.DataError) ('22018', "[22018] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Conversion failed when converting the nvarchar value '2022-01-03' to data type int. (245) (SQLExecDirectW)")
[SQL: SELECT raw.tags.[Id], raw.tags.[TagName], raw.tags.[Count], raw.tags.[ExcerptPostId], raw.tags.[WikiPostId]
FROM raw.tags
WHERE raw.tags.[Id] >= ? ORDER BY raw.tags.[Id]]
[parameters: ('2022-01-03',)]
BuzzCutNorman commented 1 year ago

I tracked the issue down to _write_starting_replication_value. It doesn't check if the replication key col is of type timestamp before assigning the start_date. I guess it could also check that the replication key is not of type numeric or int.