Hallo I have a problem I can’t solve with Extractor for MSSQL, tap-mssql --variant buzzcutnorman The MSSQL DB has a column name which is called index, which is of course a keyword. Error is pymssql._mssql.mssqldatabaseexception Incorrect syntax near ‘index’ . If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required ... My question is how can I edit this. I found that I could use stream maps (https://sdk.meltano.com/en/latest/stream_maps.html). Unfortunately it still doesn’t work for me because I guess I haven’t found the right syntax how I can use it. Could someone help me sort this out?

If possible could you please provided the stack trace of the error.

Unfortunately I can’t post here the stack trace of the error. But the parameters are so -> [parameters :({‘id’: 1, ‘index’: 0, ...<SOME_OTHER_PARAMETERS>})] Full Error message -> sqlalchemy.exc.OperationalError: (pymssql.exceptions.OperationalError) (1018, b”Incorrect syntax near ‘index’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See See SQL Server Books Online for proper synatx.DB-Lib error message 20018, serverity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n”) I think the problem is because the index has single quotation. The T-SQL syntax should be [index] for that case.

No worries you have given me enough info to create a test scenario that will hopefully recreate the same error for me.

What is even more interesting is that the table with columns is created but the error occurs only when the data is copied to a new destination.

That is great info to know thanks 🙏 for sharing that.

Thank you I will look at it on Monday because I don’t have access from home.

Nice, you made good progress. You have worked through the basic troubleshooting and have a good understanding of the issue you are up against. A stream map for the

Index

stream property might be the quickest path to a resolution.

Please, could you also advise me how I could fix it because I have several Keywords as column name in different tables. I tried the

meltano-map-transform

as well but without success.

Michal, @Edgar Ramírez (Arch.dev) helped me understand how to properly configure a singer-sdk plugin with a stream map. The first trick is that the

stream_maps

needs to be in the

config:

. This definition crosswalk was helpful to me when looking at the source database and stream map config. • stream = table • property = column I found an example that showed how you can

__NULL__

or remove a stream's property but then match the property's value to a new property name. A config like this one should get you past the issue.

plugins:
  extractors:
  - name: tap-mssql
    variant: buzzcutnorman
    pip_url: git+<https://github.com/BuzzCutNorman/tap-mssql.git>
    config:
      dialect: mssql
      driver_type: pymssql
      host: server 
      user: user
      database: database
      stream_maps:
        dbo-Redirect:
          Index: __NULL__
          ColumnIndex: index
    select:
    - 'dbo-Redirect.*'

When I get tap-mssql upgraded to SDK 0.37 you can do a more global fix I think. https://github.com/meltano/sdk/releases/tag/v0.37.0

Thank you for the quick answer but it still doesn’t work. When I additionally cast the column to str(Index) there was another error: jsonschema.exceptions.ValidationError: ‘Index’ is a required property and Failed validating ‘required’ in schema.

stream_maps:

dbo-Redirect:

Index: __NULL__

index_field: str(Index)

If you are evaluating and not in production you can use this target-mssql until you get the storebrand version working. I just gave it a test and it is worked with my testing table. This target is not production ready and not as refined as the default storebrand one. You will need to set it up as a custom plugin here is the link to that documentation https://docs.meltano.com/guide/plugin-management/#custom-plugins. Here are the settings that I put in to my meltano.yaml file when I use this target.

loaders:
  - name: target-mssql
    namespace: target_mssql
    pip_url: git+<https://github.com/BuzzCutNorman/target-mssql.git>
    capabilities:
    - about
    - stream-maps
    - schema-flattening
    - target-schema
    settings:
    - name: dialect
    - name: driver_type
    - name: host
    - name: port
      kind: integer
    - name: user
    - name: password
      kind: string
      sensitive: true
    - name: database
    - name: driver
    - name: default_target_schema
    - name: sqlalchemy_url_query
      kind: object
    - name: driver
    - name: sqlalchemy_eng_params
      kind: object
    - name: fast_executemany
    - name: future
    - name: TrustServerCertificate
    - name: batch_config
      kind: object
    - name: encoding
      kind: object
    - name: format
    - name: compression
    - name: storage
      kind: object
    - name: root
    - name: prefix
    - name: start_date
      value: '2010-01-01T00:00:00Z'
    - name: hd_jsonschema_types
      kind: boolean
    config:

fwiw I think bumping tap-mssql to at least singer-sdk v0.35.1 would get rid of

*'Index' is a required property* and *Failed validating 'required' in schema.*

thanks to https://github.com/meltano/sdk/pull/2225

ah I miss understood the error. I thought it was on the target side. There is a PR to bump tap-mssql to SDK 0.36.1. I will work of finishing my testing and merge that PR today or tomorrow.

@BuzzCutNorman and @Edgar Ramírez (Arch.dev) thank you for the very quick answers and solutions to the problem. I’ll check it out tomorrow.

I merged the Singer-SDK 0.36.1 PR to tap-mssql and published it as the latest release. You should be able to upgrade to it if you run

meltano lock --update

then

meltano install --clean

.

Sorry but I haven’t had time to test it yet, I’ll get to it on Friday and let you know.

Cool

@BuzzCutNorman it works! Thank you for your effort.