Microsoft SQL Server

This source extracts metadata from a Microsoft SQL Server database server.

Overview

Harvesting with this source extracts the following:

  • Databases, schemas, views, and tables metadata

  • Columns and column types

  • Jobs and stored procedures

This source supports:

Pre-requisites

To harvest jobs and stored procedures, you must grant the following privileges to the database user performing the harvesting:

USE MSDB
GRANT SELECT ON OBJECT::msdb.dbo.sysjobsteps TO 'username'
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO 'username'

USE 'database_name'
GRANT VIEW DEFINITION TO 'username'
GRANT SELECT ON OBJECT::sys.sql_expression_dependencies TO 'username'

Sample recipe

Example 1. File Source sample recipe.
source:
  type: mssql
  config:
    host_port: localhost:1433
    database: semarchyDemoDatabase
    username: username
    password: password

sink:
  # sink config

Parameters

The following table lists the source parameters.

Parameter

Description

host_port

Database server URL and port. This parameter is mandatory.

username

Database server user.

password

This user’s password.

include_descriptions

Set to True to collect table descriptions. Defaults to True.

include_jobs

Set to True to harvest SQL Server jobs. Requires access to the 'msdb' and 'sys' schema. Defaults to True.

include_stored_procedures

Set to True to harvest SQL Server stored procedures. Requires access to the 'sys' schema. Defaults to True.

convert_urns_to_lowercase

Set to True to convert dataset URNs to lowercase. Defaults to False.

database

Database name to harvest. If not set, all databases are harvested.

include_tables

Set to True (default) to harvest tables.

include_table_location_lineage

Set to True (default) to include table lineage to the underlying storage location.

include_views

Set to True (default) to harvest views.

include_view_lineage

Set to True (default) to parse the views' SQL to populate dataset-level table-to-view and view-to-view lineage.

include_view_column_lineage

Set to True (default) to parse the views' SQL to populate column-level table-to-view and view-to-view lineage, if include_view_lineage is set to True.

incremental_lineage

Set to True to emit lineage as incremental to existing lineage. Defaults to False.

database_pattern

Lists of regular expressions patterns to define the databases to include (allow) or exclude (deny) in the harvesting process.
Default value is {'allow': ['.*'], 'deny': [], 'ignoreCase': True}.
The ìgnoreCase option ignores case sensitivity during pattern matching. Note that database patterns are not used if the database is provided by another configuration parameter.

schema_pattern

Lists of regular expressions patterns to define the schemas to include (allow) or exclude (deny) in the harvesting process.
The resular expression is only for the schema name. For example, to match all tables in the customerB2C schema, use the customerB2C regex.
Default value is {'allow': ['.*'], 'deny': [], 'ignoreCase': True}.
The ìgnoreCase option ignores case sensitivity during pattern matching.

table_pattern

Lists of regular expressions patterns to define the tables to include (allow) or exclude (deny) in the harvesting process.
The resular expression should match the full table name (database.schema.table). For example, to match all tables in the customerB2C schema of public schema the semarchy database, use the semarchy.public.customerB2C.* regex.
Default value is {'allow': ['.*'], 'deny': [], 'ignoreCase': True}.
The ìgnoreCase option ignores case sensitivity during pattern matching.

view_pattern

Lists of regular expressions patterns to define the views to include (allow) or exclude (deny) in the harvesting process.
The resular expression should match the full view name (database.schema.view). For example, to match all views in the customerB2C schema of public schema the semarchy database, use the semarchy.public.customerB2C.* regex.
Default value is {'allow': ['.*'], 'deny': [], 'ignoreCase': True}.
The ìgnoreCase option ignores case sensitivity during pattern matching. Note that this option defaults to the table_pattern value, if set.

platform_instance

Platform instance all assets belong to.

env

Environment assets belong to. Defaults to PROD.