Skip to content

Scanner for MSSQL

You can scan metadata details such as schemas, tables, view details etc. from MSSQL with depot/non-depot Scanner workflows. In this document, find requirements and YAML configurations to connect to MSSQL for extracting entity metadata.

Requirements

  • Credentials to connect to MSSQL database.
  • To extract metadata, the MSSQL user needs to have access to the INFORMATION_SCHEMA.
  • MSSQL User must grant SELECT privilege to fetch the metadata of tables and views.

Depot Scan Workflow

DataOS allows you to connect to a database with JDBC driver to read data from tables using Depot. You can also scan metadata from an MSSQL-type depot with Scanner workflows.The Depot enables access to all schemas visible to the specified user in the configured database, MSSQL.

You can apply database, schema, and table filter patterns while scanning metadata.

version: v1
name: mssql-scanner2
type: workflow
tags:
  - mssql-scanner2.0
description: The job scans schema tables and register metadata
workflow:
  dag:
    - name: scanner2-mssql
      description: The job scans schema from Mssql depot tables and register metadata to metis2
      spec:
        stack: scanner:2.0
        compute: runnable-default
        runAsUser: metis
        stackSpec:
          depot: mssql01
          sourceConfig:
            config:
              markDeletedTables: false
              includeTables: true
              includeViews: true
              databaseFilterPattern:
                  includes:
                      - database1
                      - database2
                    excludes:
                      - database3
                      - database4
                  schemaFilterPattern:
                    includes:
                      - schema1
                      - schema2
                    excludes:
                      - schema3
                      - schema4
                  tableFilterPattern:
                    includes:
                      - table1
                      - table2
                    excludes:
                      - table3
                      - table4

After the successful workflow run, you can check the metadata of scanned Tables on Metis UI for all schemas present in the database.