MSSQL Data Virtualization Wizard

This VS Code extension provides a step-by-step wizard to connect external SQL Server databases via PolyBase and automatically generate CREATE EXTERNAL TABLE statements.

Note: As confirmed by Microsoft in this discussion, "As of now, there isn't a direct replacement for the Virtualize Data extension in VS Code, neither we have planned to support this feature in the MSSQL extension." This community extension fills that gap.

Changelog

• 0.4.0: Improved SQL formatting — refactored CREATE EXTERNAL TABLE column definitions to display one column per line with proper indentation; dependency updates (VS Code 1.125, @types/vscode 1.125, vscode-mssql v1.43.0)
• 0.3.3: Maintenance release — dependency updates (VS Code 1.120, @types/vscode 1.120, vscode-mssql typings v1.42.2)
• 0.3.2: TypeScript 6.0.2 upgrade with tsconfig moduleResolution fix, updated @types/vscode to 1.116.0
• 0.3.1: Maintenance release with dependency and compatibility updates (VS Code 1.110 support, @types/vscode 1.110, underscore and undici updates)
• 0.3.0: Add support for Oracle external datasources + update vscode-mssql typing to 1.40
• 0.2.1: Add functionality to interrupt the wizard.
• 0.2.0: Add support for MariaDB / MySQL external datasources
• 0.1.0: Support for SQL Server external datasources

Features

The wizard executes the following steps:

1. Select Connection: Choose a SQL Server database connection
2. Select Database: Choose the target database where external tables will be created
3. Select Provider Type: Choose SQL Server, MariaDB/MySQL, or Oracle discovery mode
4. Select Destination Schema: Choose the schema for created external tables (default: dbo)
5. Select External Data Source: Choose an existing PolyBase external data source
6. Discover External Databases: Select one or more external databases/schemas to connect to
7. Select Tables/Views: Choose which tables and views you want to virtualize
8. Generate Scripts: The wizard automatically detects the schemas of external objects and generates corresponding CREATE EXTERNAL TABLE statements
9. Review Scripts: The generated DDL is displayed in a new SQL editor tab
10. Cleanup: All temporary discovery tables are automatically removed

Usage

To start the wizard:

1. Press CTRL + Shift + P (or CMD + Shift + P on macOS) to open the Command Palette
2. Type and select "Virtualize Data Wizard"
3. Follow the step-by-step wizard to generate your external table scripts

How It Works

The wizard leverages PolyBase schema detection:

• For each selected table or view, the wizard sends a dummy CREATE EXTERNAL TABLE statement to SQL Server
• SQL Server returns an error message containing the actual schema of the external object
• The wizard parses this detected schema and generates a correct CREATE EXTERNAL TABLE statement
• After detection, all temporary external tables are automatically cleaned up

Prerequisites

• VS Code 1.70.0 or higher
• MSSQL extension (ms-mssql.mssql) - this is a required dependency. This extension manages database connections. For setup instructions, see the vscode-mssql repository
• SQL Server 2016 or higher with PolyBase installed and configured. For PolyBase setup instructions, see the PolyBase guide
• Existing PolyBase external data source configured in the target database. For external data source configuration, see CREATE EXTERNAL DATA SOURCE
• Network access from your SQL Server to the external database

Required SQL Server Privileges

The user account must have the following permissions in the target database:

• CREATE EXTERNAL TABLE: Required to create external tables
• ALTER SCHEMA: Required if creating external tables in non-dbo schemas
• SELECT on sys.external_data_sources: Required to discover available external data sources
• SELECT on sys.schemas, sys.tables, and sys.views: Required to query metadata of external objects (these are queried via temporary external tables)
• ALTER DATABASE: May be required if PolyBase is not yet enabled on the database

Minimal recommended role: db_owner or custom role with the above permissions.

Supported External Data Sources

• SQL Server external data sources via PolyBase (using sqlserver:// location)
• MariaDB/MySQL external data sources via PolyBase + ODBC (using odbc:// location)
• Oracle external data sources via PolyBase (using oracle:// location, see Oracle PolyBase configuration)

Other PolyBase sources (Azure Blob Storage, Hadoop, etc.) are not supported at this time.

Oracle-Specific Notes

When working with Oracle external data sources:

• Use the built-in oracle:// connection string format (e.g., oracle://hostname:1521)
• In Oracle, a schema equals a user. The wizard discovers schemas by querying ALL_USERS
• System schemas (SYS, SYSTEM, etc.) are automatically filtered out
• Oracle table names are typically uppercase (e.g., AVIATION.AIRPLANE)
• The LOCATION in generated external tables uses the format: SCHEMA.TABLE (e.g., AVIATION.AIRPLANE)

Example Oracle external data source creation:

CREATE DATABASE SCOPED CREDENTIAL OracleCredential
WITH IDENTITY = 'SYSTEM', SECRET = 'YourPassword';

CREATE EXTERNAL DATA SOURCE Oracle_Aviation
WITH (
    LOCATION = 'oracle://localhost:1521',
    CREDENTIAL = OracleCredential
);

Test Infrastructure

See tst/README.md for a Docker/Podman compose environment with:

• SQL Server (PolyBase enabled)
• Sample SQL Server database (Movies)
• MariaDB instance (Books database)
• Oracle Free instance (Aviation database)

Note on Temporary Tables

During the discovery process, the wizard creates temporary external tables (with prefix DVW_):

• For SQL Server:

  • DVW_sys_databases_*: For discovering available external databases
  • DVW_*_sys_tables_*: For discovering tables in external databases
  • DVW_*_sys_views_*: For discovering views in external databases
  • DVW_*_sys_schemas_*: For discovering schema information

• For MariaDB/MySQL:

  • DVW_information_schema_schemata_*: For discovering available databases
  • DVW_*_information_schema_tables_*: For discovering tables and views

• For Oracle:

  • DVW_ALL_USERS_*: For discovering available schemas/users
  • DVW_*_ALL_TABLES_*: For discovering tables in schemas

These tables are automatically removed at the end of the process. They are temporary and exist solely to support the discovery process.