Hi Everyone, I'm using this variant "buzzcutnorman...
# getting-started
d
Hi Everyone, I'm using this variant "buzzcutnorman" of the mssql extractor. However, I'm having some challenges connecting to it. Below is the error when I test the connection.
Copy code
sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: <https://sqlalche.me/e/14/rvf5>)
b
Hmm sorry to hear that. Here is a sample config that you can check yours against.
Copy code
config:
      dialect: mssql
      driver_type: pyodbc
      host: <yourserver>
      port: 1433
      user: <username>
      database: <database>
      sqlalchemy_eng_params:
        fast_executemany: 'True'
      sqlalchemy_url_query:
        driver: ODBC Driver 18 for SQL Server
        TrustServerCertificate: yes
d
Thanks, I will try that
b
The other thing I would check is if you have the
Microsoft ODBC Driver for SQL Server
installed. You need the config line of
driver: ODBC Driver <NN> for SQL Server
to match the installed version. You can change out <NN> for the version number of the driver. If you need the installation instruction they are here: Install the Microsoft ODBC driver for SQL Server (Linux)
c
You can also use
pymssql
(which uses FreeTDS instead of Microsoft's proprietary blobs) as the alternative driver option.
d
The odbc driver is installed by I'm still getting the same error unfortunately. I changed the Driver_type to pymssql but that also gives this error:
Need help fixing this problem? Visit http://melta.no/ for troubleshooting steps, or to join our friendly Slack community. Plugin configuration is invalid Catalog discovery failed: command ['/home/dwhadmin/meltano-projects/my-sql-proj/.meltano/extractors/tap-mssql/venv/bin/tap-mssql', '--config', '/home/dwhadmin/meltano-projects/my-sql-proj/.meltano/run/tap-mssql/tap.b7f902dc-4d39-4cfb-b1c1-4ae8e988ece8.config.json', '--discover'] returned 1 with stderr: Config validation failed: True is not of type 'string', 'null' JSONSchema was: {'type': 'object', 'properties': {'dialect': {'type': ['string', 'null'], 'description': 'The Dialect of SQLAlchamey'}, 'driver_type': {'type': ['string', 'null'], 'description': 'The Python Driver you will be using to connect to the SQL server'}, 'host': {'type': ['string', 'null'], 'description': 'The FQDN of the Host serving out the SQL Instance'}, 'port': {'type': ['integer', 'null'], 'description': 'The port on which SQL awaiting connection'}, 'user': {'type': ['string', 'null'], 'description': 'The User Account who has been granted access to the SQL Server'}, 'password': {'type': ['string', 'null'], 'description': 'The Password for the User account'}, 'database': {'type': ['string', 'null'], 'description': 'The Default database for this connection'}, 'sqlalchemy_eng_params': {'type': ['object', 'null'], 'properties': {'fast_executemany': {'type': ['string', 'null'], 'description': 'Fast Executemany Mode: True, False'}, 'future': {'type': ['string', 'null'], 'description': 'Run the engine in 2.0 mode: True, False'}}, 'description': 'SQLAlchemy Engine Paramaters: fast_executemany, future'}, 'sqlalchemy_url_query': {'type': ['object', 'null'], 'properties': {'driver': {'type': ['string', 'null'], 'description': 'The Driver to use when connection should match the Driver Type'}, 'TrustServerCertificate': {'type': ['string', 'null'], 'description': 'This is a Yes No option'}}, 'description': 'SQLAlchemy URL Query options: driver, TrustServerCertificate'}, 'batch_config': {'type': ['object', 'null'], 'properties': {'encoding': {'type': ['object', 'null'], 'properties': {'format': {'type': ['string', 'null'], 'description': 'Currently the only format is jsonl'}, 'compression': {'type': ['string', 'null'], 'description': 'Currently the only compression options is gzip'}}}, 'storage': {'type': ['object', 'null'], 'properties': {'root': {'type': ['string', 'null'], 'description': 'the directory you want batch messages to be placed in\nexample: file://test/batches'}, 'prefix': {'type': ['string', 'null'], 'description': 'What prefix you want your messages to have\nexample: test-batch-'}}}}, 'description': 'Optional Batch Message configuration'}, 'start_date': {'type': ['string', 'null'], 'format': 'date-time', 'description': 'The earliest record date to sync'}, 'stream_maps': {'type': ['object', 'null'], 'properties': {}, 'description': 'Config object for stream maps capability. For more information check out [Stream Maps](https://sdk.meltano.com/en/latest/stream_maps.html).'}, 'stream_map_config': {'type': ['object', 'null'], 'properties': {}, 'description': 'User-defined config values to be used within map expressions.'}, 'flattening_enabled': {'type': ['boolean', 'null'], 'description': "'True' to enable schema flattening and automatically expand nested properties."}, 'flattening_max_depth': {'type': ['integer', 'null'], 'description': 'The max depth to flatten schemas.'}}} Traceback (most recent call last): File "/home/dwhadmin/meltano-projects/my-sql-proj/.meltano/extractors/tap-mssql/venv/bin/tap-mssql", line 8, in <module> sys.exit(Tapmssql.cli()) File "/home/dwhadmin/meltano-projects/my-sql-proj/.meltano/extractors/tap-mssql/venv/lib/python3.8/site-packages/click/core.py", line 1130, in call return self.main(*args, **kwargs) File "/home/dwhadmin/meltano-projects/my-sql-proj/.meltano/extractors/tap-mssql/venv/lib/python3.8/site-packages/click/core.py", line 1055, in m…
c
ModuleNotFoundError: No module named 'pymssql'
Yup. I just saw that. The
pymssql
support as an alternate driver does require you to manually add the required Python package into your
meltano.yml
definition. I'm actually not quite sure if we can use
extras
in meltano (i.e. if we could use
pip_url: tap-mssql[pymssql]
in meltano.yml)
d
@christoph thanks for the response. I'm not sure how that will be added considering the pip_url value is pip_url: git+https://github.com/BuzzCutNorman/tap-mssql.git
c
So, right now as a workaound to make the
pymssql
driver fully available in your meltano project, you will need to edit your
meltano.yml
and adjust this line:
Copy code
pip_url: git+<https://github.com/BuzzCutNorman/tap-mssql.git>
And change it to this:
Copy code
pip_url: git+<https://github.com/BuzzCutNorman/tap-mssql.git> pymssql
And then run
meltano install --clean
You also need to make sure that you have the
FreeTDS
library packages for your OS installed (e.g.
apt-get install freetds-dev
in Ubuntu)
d
Thank you very much @christoph that worked!!!!! ✔️
c
Sounds great. Btw., we found out that the
FreeTDS
library now no longer needs to be pre-installed into your OS. The binary wheels for
pymssql
are now built for many different platforms and include the correct copies of latest versions of the native library for FreeTDS in the binary wheel on PyPI
d
Good to know, thanks for the information 🙂