Microsoft SQL Server - Lineage
This article outlines the lineage coverage, configuration requirements, metadata handling, supported scenarios, component behaviors, transformation coverage, and known limitations for lineage extraction in Microsoft SQL Server. The lineage process provides end-to-end visibility into data movement across SQL Server datasets and transformations. It supports table-level and column-level lineage for standard SQL workloads, helping organizations track data dependencies and perform impact analysis efficiently.
Lineage Configuration Requirements
Accurate lineage extraction depends on SQL Server connectivity, metadata availability, and access to dependent objects. These requirements must be properly configured and available to ensure successful lineage generation and source-target resolution.
Configuration Requirements Table
SQL Server Connection
Secure access to Microsoft SQL Server must be configured
Metadata Access
Permissions to read database objects and metadata are required
Dependent Object Availability
All dependent objects should be available for lineage processing
Optional Lineage Configurations
Optional configurations can be enabled to improve the handling of complex lineage scenarios
Missing permissions, inaccessible database objects, or unavailable metadata may result in incomplete lineage generation.
Lineage Components
Tables
✅
Views
✅
Stored Procedures
✅
Triggers
✅
Functions
⚠️
Temp Tables
⚠️
Synonyms
⚠️
Linked Server (OPENQUERY)
⚠️
Nested Procedures
⚠️
Cross-Connection Objects
⚠️
Dynamic SQL
❌
Encrypted Objects
❌
The ⚠️ icon indicates partially supported functionality with limited lineage coverage in applicable scenarios.
Column Creation Support
This section outlines lineage support for column creation and transformation scenarios.
Column Creation from Lineage
Direct Column Mapping
✅
Derived Columns
✅
Aggregations (SUM, MAX, etc.)
⚠️
Expressions / CASE Statements
⚠️
Aliases
⚠️
SELECT * Expansion
⚠️
Dynamic Column Generation
❌
The ⚠️ icon indicates partially supported functionality with limited lineage coverage in applicable scenarios.
Supported Use Cases
The connector supports lineage extraction across standard SQL Server workloads and object relationships. These use cases represent scenarios where lineage extraction functions as expected.
Supported Lineage Scenarios
View-Based Transformations
Lineage extraction across views and underlying source objects
Stored Procedure Workflows (Static SQL)
Lineage extraction from stored procedures containing static SQL
Query Log Lineage
Lineage generated from available query execution information
Cross-Schema Lineage
Lineage across schemas within the same SQL Server environment
Lineage extraction is based on accessible SQL definitions, metadata, and supported SQL Server objects available through the configured connection.
Partial or Limited Coverage
Certain scenarios provide only partial lineage coverage due to query complexity, object resolution limitations, or metadata dependencies.
Scenarios
Functions (UDFs)
Partial lineage coverage for user-defined functions
Temp Tables (#)
Lineage may be incomplete for temporary tables
Linked Server (OPENQUERY)
Limited SQL parsing and object resolution
Nested Procedures
Partial resolution of procedure dependencies
Cross-Connection Lineage
Coverage depends on configuration and metadata availability
Complex SQL Logic
Reduced lineage accuracy for complex SQL transformations
Advanced SQL logic and cross-system dependencies may result in incomplete lineage paths or reduced column-level lineage accuracy.
Unsupported Scenarios
The connector does not support lineage extraction for certain runtime-generated, encrypted, or external processing patterns due to limitations in metadata availability and SQL accessibility.
Unsupported Lineage
Dynamic SQL (EXEC, runtime queries)
No lineage generated
Use query logs or manual lineage
Encrypted Objects
SQL definitions cannot be read
Remove encryption or document manually
Runtime-generated Tables/Columns
No lineage generated
Use static SQL alternatives
Unsupported SQL Syntax
Parsing failure
Simplify SQL or use dataset correction
External/Non-SQL Logic (CLR, scripts)
Not captured in lineage
Document separately
Unsupported scenarios will not produce lineage and may appear disconnected in lineage visualization.
Current Functional Status
This section outlines the present state of lineage coverage supported by the Microsoft SQL Server connector based on the available capabilities and limitations.
Overall Coverage
Strong coverage across standard SQL Server workloads
Lineage Depth
Reliable table-level and column-level lineage
Supported Inputs
Tables, views, stored procedures, triggers, query logs, and supported database objects
Functional Scope
Lineage extraction for standard SQL transformations and object dependencies
Limitation Areas
Functions, temp tables, linked servers, nested procedures, cross-connection lineage, and complex SQL logic
Resulting Output
Reliable lineage for most SQL Server workloads with partial coverage for advanced scenarios
The Microsoft SQL Server connector is production-ready with strong support for standard SQL Server workloads, including reliable table-level and column-level lineage. Advanced scenarios such as complex SQL logic, cross-connection lineage, linked server queries, and user-defined functions may have partial coverage depending on implementation complexity and metadata availability.
Copyright © 2026, OvalEdge LLC, Peachtree Corners GA USA
Last updated
Was this helpful?