> For the complete documentation index, see [llms.txt](https://docs.ovaledge.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.ovaledge.com/connectors/connector-repositories/etl-tool/microsoft-sql-server-integration-services-ssis/microsoft-sql-server-integration-services-lineage.md).

# Microsoft SQL Server Integration Services - Lineage

This article outlines the lineage coverage, configuration settings, metadata handling, supported scenarios, component behavior, process flow, and known limitations for lineage extraction in SQL Server Integration Services (SSIS). The lineage process captures how data moves across SSIS packages by connecting sources, destinations, transformations, variables, connection managers, and control flow tasks. It supports table-level and column-level lineage to trace data flow from source tables or files through transformation components to destination systems, enabling transparency, impact analysis, dependency tracking, and operational visibility across ETL workflows.

### Lineage Configuration Requirements

Accurate lineage extraction depends on specific configuration settings and access permissions. These settings must be configured correctly to ensure that SSIS packages, connections, transformations, and source-to-target mappings are interpreted successfully.

#### Configuration Requirements Table

<table><thead><tr><th width="215">Configuration</th><th>Required Detail</th></tr></thead><tbody><tr><td>Database Access</td><td>Read access to the underlying source and target databases is required</td></tr><tr><td>Package Encryption</td><td>Encrypted packages must be decrypted before lineage extraction</td></tr><tr><td>Connection Information</td><td>Connection manager details must be accessible for source and destination mapping</td></tr><tr><td>Variable Handling</td><td>Static variable values must be available for accurate lineage resolution</td></tr><tr><td>Package Scope</td><td>Supports processing specific packages or all packages through the datasetId configuration</td></tr><tr><td>Connection Strings</td><td>Requires decrypted connection strings for lineage processing</td></tr></tbody></table>

{% hint style="warning" %}
Incorrect permissions, inaccessible package files, encrypted packages, unresolved connection information, or missing variable values may prevent lineage extraction or result in incomplete lineage mapping.
{% endhint %}

### Lineage Components

<table><thead><tr><th width="417">Component</th><th align="center">Availability</th></tr></thead><tbody><tr><td>Relational and database sources (OLE DB, ODBC, ADO.NET, Oracle, PostgreSQL, SQL Server)</td><td align="center">✅</td></tr><tr><td>File sources (Flat files, CSV, TXT, Excel, HDFS)</td><td align="center">✅</td></tr><tr><td>SharePoint lists</td><td align="center">✅</td></tr><tr><td>Dynamic SQL or unsupported source types</td><td align="center">❌</td></tr><tr><td>Relational and database destinations (OLE DB, ODBC, ADO.NET, Oracle, PostgreSQL, SQL Server)</td><td align="center">✅</td></tr><tr><td>File destinations (Flat files, CSV, TXT, Excel, HDFS)</td><td align="center">✅</td></tr><tr><td>Dynamic or encrypted destinations</td><td align="center">❌</td></tr><tr><td>Type conversions, sorting, joins (Merge Join, Lookup), and aggregations (SUM, AVG, COUNT)</td><td align="center">✅</td></tr><tr><td>Derived Columns (basic expressions)</td><td align="center">✅</td></tr><tr><td>Union All (data stream merges)</td><td align="center">✅</td></tr><tr><td>Execute SQL Task, Execute Package Task, and Data Flow Task</td><td align="center">✅</td></tr><tr><td>ForEach Loop and Sequence Containers</td><td align="center">✅</td></tr><tr><td>Script Task, File System Task, and FTP Task</td><td align="center">❌</td></tr></tbody></table>

## Supported Use Cases

The connector supports several SSIS lineage scenarios across packages, sources, destinations, transformations, and control flow tasks. These use cases describe areas where lineage extraction functions as expected for standard SSIS implementations.

### Supported Lineage Scenarios

<table><thead><tr><th width="229.66668701171875">Supported</th><th>Details</th></tr></thead><tbody><tr><td>Standard SSIS Packages</td><td>Lineage extraction for standard SSIS package files (.dtsx)</td></tr><tr><td>Relational Source and Destination Lineage</td><td>Lineage extraction for OLE DB, ODBC, ADO.NET, Oracle, PostgreSQL, and SQL Server sources and destinations</td></tr><tr><td>File-Based Lineage</td><td>Lineage extraction from flat files, CSV/TXT, Excel, and HDFS sources and destinations</td></tr><tr><td>SharePoint List Lineage</td><td>Lineage extraction from SharePoint list sources</td></tr><tr><td>Transformation Lineage</td><td>Lineage extraction for Data Conversion, Sort, Merge Join, Derived Column (basic expressions), Union All, Lookup, and Aggregate transformations</td></tr><tr><td>Control Flow Task Lineage</td><td>Lineage extraction for Execute SQL Task, Execute Package Task, Data Flow Task, ForEach Loop, and Sequence Container tasks</td></tr></tbody></table>

{% hint style="warning" %}
Column-level lineage is supported for standard source-to-transformation-to-destination mappings across supported SSIS components.
{% endhint %}

## Partial or Limited Coverage

Certain scenarios function only with partial coverage due to limitations in metadata availability, transformation complexity, or runtime-generated logic. These areas may produce incomplete lineage results.

### Scenarios

<table><thead><tr><th width="234.33331298828125">Scenario</th><th>Limitation Description</th></tr></thead><tbody><tr><td>Complex Expressions and CASE Statements</td><td>Complex expressions, nested CASE statements, and multi-step transformation logic may not resolve fully</td></tr><tr><td>Nested Derived Columns</td><td>Derived column transformations with nested expressions are partially tracked</td></tr><tr><td>Column Concatenations</td><td>Lineage tracks only the first participating column in concatenation operations</td></tr><tr><td>Aggregate Functions</td><td>Aggregate functions such as SUM, AVG, and COUNT are partially tracked during transformation mapping</td></tr><tr><td>Static Runtime Variables</td><td>Runtime variables are supported only when values are predefined and accessible during lineage extraction</td></tr></tbody></table>

{% hint style="warning" %}
Incomplete metadata, unresolved variables, failed query parsing, or unsupported transformation logic may prevent complete lineage creation.
{% endhint %}

## Unsupported Scenarios

The connector does not support lineage extraction for runtime-generated logic, unsupported SSIS components, encrypted packages, or unsupported processing behaviors due to the absence of accessible metadata.

### Unsupported Lineage

<table><thead><tr><th width="241">Not Supported</th><th>Description</th></tr></thead><tbody><tr><td>Dynamic SQL or Runtime-Generated Queries</td><td>Runtime-generated or parameterized SQL queries are not resolved during lineage extraction</td></tr><tr><td>Script Tasks</td><td>C# and VB.NET script logic is not parsed for lineage</td></tr><tr><td>Encrypted Packages Without Decryption</td><td>Packages cannot be processed unless decrypted before extraction</td></tr><tr><td>Custom or Third-Party Components</td><td>Custom SSIS components and external third-party components are unsupported</td></tr><tr><td>Cloud Connectors or Azure Sources</td><td>Lineage extraction for cloud-native or Azure-based connectors is not supported</td></tr><tr><td>Failed Query Parsing for Complex SQL</td><td>Unsupported or highly complex SQL queries may fail during parsing</td></tr><tr><td>Wildcard File Patterns</td><td>Wildcard patterns such as *.txt are not resolved during lineage extraction</td></tr><tr><td>Conditional Precedence Constraints</td><td>Conditional execution flows and precedence-based task logic are not tracked</td></tr></tbody></table>

{% hint style="info" %}
Unsupported scenarios will not produce lineage and may appear disconnected in lineage visualization.
{% endhint %}

## Current Functional Status

This section outlines the present state of lineage coverage supported by the SSIS connector based on the available capabilities and limitations.

<table><thead><tr><th width="200.33331298828125">Status Area</th><th>Details</th></tr></thead><tbody><tr><td>Overall Coverage</td><td>Advanced coverage for standard SSIS packages, sources, transformations, and control flow tasks</td></tr><tr><td>Lineage Depth</td><td>Table-level and column-level lineage are supported for standard SSIS implementations</td></tr><tr><td>Supported Inputs</td><td>SSIS packages, relational databases, file-based sources, transformations, variables, and control flow tasks</td></tr><tr><td>Functional Scope</td><td>Lineage extraction works for supported SSIS lineage scenarios and standard ETL workflows</td></tr><tr><td>Partial Coverage Areas</td><td>Complex expressions, CASE statements, nested derived columns, aggregate tracking, and runtime variables</td></tr><tr><td>Unsupported Areas</td><td>Script tasks, dynamic SQL, encrypted packages, custom components, wildcard files, cloud connectors, and conditional flows</td></tr><tr><td>Resulting Output</td><td>Lineage is extracted for most standard SSIS implementations, with partial or unavailable mapping in unsupported scenarios</td></tr></tbody></table>

{% hint style="warning" %}
Coverage remains partial for complex runtime logic, dynamic SQL, nested expressions, encrypted packages, and unsupported SSIS components because these scenarios do not expose sufficient metadata for complete lineage extraction.
{% endhint %}

***

Copyright © 2026, OvalEdge LLC, Peachtree Corners GA USA


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.ovaledge.com/connectors/connector-repositories/etl-tool/microsoft-sql-server-integration-services-ssis/microsoft-sql-server-integration-services-lineage.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.