target-mssql is a Singer target for mssql. !!! Warning !!! really early version. It works ok 😐. Not doing native bcp loads yet.
Built with the Meltano Target SDK.
2024-08-20 msgspec and more: I have been working on getting a quicker JSON decoder in place for a while and thanks to Edger at Arch I am able too. The library I switched to is msgspec. It is lightweight and fast. Big Thank You 🙏 to Jim Crist-Harif for writing and maintaining msgspec. I also removed pedulum and am using phython datetime at the moment. I enabled overwrite load_method. Considered yourself warned that, There be monsters 🧌 of ye own making using the overwrite method. 😁
Last but not least all batch message records are pre-processed and datetimes parsed just like orginal non-batch loads. This resolves that long standing issue which stoped this target from accepting batch messages. I can finally take off my thinking cap and go back to regular claping. If you understand that reference you have been here a while and Thanks for the support.
2024-08-01 Upgraded to Meltano Singer-SDK 0.39.0
2024-07-15 Upgraded to Meltano Singer-SDK 0.36.1
2024-01-31 Upgraded to Meltano Singer-SDK 0.34.1: Happy New Year!!!🎉. My goal was to start using tags and releases by 2024 and was pretty close. You can now lock on a release number if you want.
2023-10-16 Upgraded to Meltano Singer-SDK 0.32.0: SQLAlchemy 2.x is main stream in this version so I took advantage of that and bumped from 1.4.x to 2.x. The issue with Windows wheels for pymssql was resolved so I bumped it back up to 2.2.8. In the hd_jsonschema_types the minimum and maximum values used to define NUMERIC or DECIMAL precision and scale values were being rounded. This caused an issue with the translation on the target side. I leveraged scientific notation to resolve this. Runs that contained streams with strings as primary keys were failing during table creation. This has been resolved by setting all primary key columns of string type to a size of 450 bytes or NVARCHAR(450).
2023-04-26 New HD JSON Schema Types: Added translations for HD JSON Schema definitions of Xml and Binary types from the buzzcutnorman tap-mssql. This is Thanks🙏 to Singer-SDK 0.24.0 which allows for JSON Schema contentMediaType and contentEncoding. Currently all Binary data types are decoded before being inserted as VARBINARY. XML types do not have the Collection XML schema just the XML content.
2023-02-08 Higher Defined(HD) JSON Schema types: Translates the Higher Defined(HD) JSON Schema types from the buzzcutnorman tap-mssql back into MS SQL data types. You can give it a try by setting hd_jsonschema_types to True in your config.json or meltano.yml
You will need to install the SQL Server Native Driver or ODBC Driver for SQL Server
Installing Microsoft ODBC Driver for SQL Server
Install from GitHub:
pipx install git+https://github.com/BuzzCutNorman/target-mssql.gitInstall using Meltano as a Custom Plugin
| Setting | Required | Default | Description |
|---|---|---|---|
| dialect | True | mssql | The Dialect of SQLAlchamey |
| driver_type | True | pymssql | The Python Driver you will be using to connect to the SQL server |
| host | True | None | The FQDN of the Host serving out the SQL Instance |
| port | False | None | The port on which SQL awaiting connection |
| user | True | None | The User Account who has been granted access to the SQL Server |
| password | True | None | The Password for the User account |
| database | True | None | The Default database for this connection |
| default_target_schema | False | None | The Default schema to place all streams |
| sqlalchemy_eng_params | False | None | SQLAlchemy Engine Paramaters: fast_executemany, future |
| sqlalchemy_eng_params.fast_executemany | False | None | Fast Executemany Mode: True, False |
| sqlalchemy_eng_params.future | False | None | Run the engine in 2.0 mode: True, False |
| sqlalchemy_url_query | False | None | SQLAlchemy URL Query options: driver, MultiSubnetFailover, TrustServerCertificate |
| sqlalchemy_url_query.driver | False | None | The Driver to use when connection should match the Driver Type |
| sqlalchemy_url_query.MultiSubnetFailover | False | None | This is a Yes No option |
| sqlalchemy_url_query.TrustServerCertificate | False | None | This is a Yes No option |
| batch_config | False | None | Optional Batch Message configuration |
| batch_config.encoding | False | None | |
| batch_config.encoding.format | False | None | Currently the only format is jsonl |
| batch_config.encoding.compression | False | None | Currently the only compression options is gzip |
| batch_config.storage | False | None | |
| batch_config.storage.root | False | None | The directory you want batch messages to be placed in. example: file://test/batches |
| batch_config.storage.prefix | False | None | What prefix you want your messages to have example: test-batch- |
| start_date | False | None | The earliest record date to sync |
| hd_jsonschema_types | False | False | Turn on translation of Higher Defined(HD) JSON Schema types to SQL Types |
| hard_delete | False | False | Hard delete records. |
| add_record_metadata | False | None | Add metadata to records. |
| load_method | False | append-only | The method to use when loading data into the destination. append-only will alwayswrite all input records whether that records already exists or not. upsert will update existing records andinsert new records. overwrite willdelete all existing records and insert all input records. |
| batch_size_rows | False | None | Maximum number of rows in each batch. |
| validate_records | False | True | Whether to validate the schema of the incoming streams. |
| stream_maps | False | None | Config object for stream maps capability. For more information check out Stream Maps. |
| stream_map_config | False | None | User-defined config values to be used within map expressions. |
| faker_config | False | None | Config for the Faker instancevariable fake used within map expressions.Only applicable if the plugin specifies faker as an addtional dependency(through the singer-sdk faker extra or directly). |
| faker_config.seed | False | None | Value to seed the Faker generator for deterministic output: https://faker.readthedocs.io/en/master/#seeding-the-generator |
| faker_config.locale | False | None | One or more LCID locale strings to produce localized output for: https://faker.readthedocs.io/en/master/#localization |
| flattening_enabled | False | None | 'True' to enable schema flattening and automatically expand nested properties. |
| flattening_max_depth | False | None | The max depth to flatten schemas. |
A full list of supported settings and capabilities for this target is available by running:
target-mssql --aboutThis Singer target will automatically import any environment variables within the working directory's
.env if the --config=ENV is provided, such that config values will be considered if a matching
environment variable is set either in the terminal context or in the .env file.
You can easily run target-mssql by itself or in a pipeline using Meltano.
See the dev guide for more instructions on how to use the Meltano Singer SDK to develop your own Singer taps and targets.