Overview¶
Alation Cloud Service Applies to Alation Cloud Service instances of Alation
Customer Managed Applies to customer-managed instances of Alation
Enhanced Connector Enhanced connectors add extended capabilities and require a separate entitlement in addition to your Alation platform license.
The Microsoft SSIS OCF connector connects to a Microsoft SQL Server Integration Services (SSIS) instance and extracts detailed, column-level lineage from your deployed SSIS packages. The connector parses the packages stored in the SSIS Catalog (SSISDB) and models each package’s sources, destinations, and transformations in Alation, so you can trace how data moves through your ETL processes without opening the package in a development environment.
Important
The connector connects directly to the SSIS Catalog database (SSISDB) on a Microsoft SQL Server instance. It does not connect to a file system to read .dtsx package files, and it does not support the legacy Package Deployment Model. Your SSIS projects must be deployed to SSISDB using the Project Deployment Model.
Important
Since SSIS is an ETL orchestration tool and not a live data source, standard Alation features that operate on live data, such as Compose, Data Sampling, and Query Log Ingestion, do not apply to this connector.
Team¶
You may need assistance from the following administrators to install and configure this connector in Alation:
SQL Server Administrator:
Creates the Windows service account for Alation and grants it the required permissions on SSISDB.
Confirms that the target SSIS projects are deployed using the Project Deployment Model.
Alation Server Administrator:
Installs the connector.
Configures the connection, metadata extraction, and cross-source lineage.
Supported Versions¶
The connector is compatible with packages from the following Microsoft SQL Server versions:
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2019
SQL Server 2022
Authentication¶
The connector authenticates to the SQL Server instance hosting SSISDB exclusively through Windows Authentication (NTLM or Kerberos). It does not support username/password authentication or certificate-based authentication.
Scope¶
The table below shows which capabilities are supported by the Microsoft SSIS OCF Connector.
Feature |
Scope |
Availability |
|---|---|---|
Authentication |
||
JDBC connection |
Used to connect to the SQL Server instance hosting SSISDB. |
Yes |
Windows Authentication (NTLM/Kerberos) |
The only supported authentication method. |
Yes |
Username/password or certificate authentication |
No |
|
Metadata Extraction |
||
Selective extraction |
Extraction can be filtered to specific packages within the SSIS Catalog. |
Yes |
Custom extraction queries |
Not Applicable |
|
Lineage |
||
Table-level lineage |
Yes |
|
Column-level lineage |
Yes |
|
Cross-source lineage (SQL Server, Oracle) |
Requires the corresponding Alation SQL Server or Oracle connector to be configured and extracted. |
Yes |
Catalog Features |
||
Compose |
Not applicable |
Not Applicable |
Data Sampling |
Not applicable |
Not Applicable |
Query Log Ingestion (QLI) |
Not applicable |
Not Applicable |
Note
Cross-source lineage requires the source or destination system to be Microsoft SQL Server or Oracle, with the corresponding Alation connector installed and a successful metadata extraction already run for the relevant database assets. For other source or destination types (for example, flat files), the connector still creates the package’s logical tables, but doesn’t link them to an external catalog entry. For configuration steps, see Configure Cross-Source Lineage.
Supported SSIS Objects¶
The following SSIS objects are supported by the connector.
Type |
SSIS Object |
|---|---|
Connection Managers |
ADO.NET, OLE DB, ODBC, Flat File, and File Connection Managers |
Sources and Destinations * |
ADO.NET Source, OLE DB Source, and ODBC Source |
Sources and Destinations |
Flat File Source and File Source |
Sources and Destinations |
ADO.NET Destination, ODBC Destination, Flat File Destination, and File Destination |
Sources and Destinations ** |
OLE DB Destination |
Containers |
Sequence Container |
Tasks |
Data Flow Task |
Tasks *** |
Execute SQL Task |
Transformations |
Derived Column, Script Component, Aggregate, Sort, Lookup, Fuzzy Lookup, Merge Join, Multicast, and Union All |
Transformations **** |
OLE DB Command |
Variables and Parameters |
User Variables |
* Support the Table, View, and SQL Command data access modes. The SQL Command mode supports only simple queries against a single table.
** Supports only the Table or View and Table or View - fast load data access modes.
*** Lacks table-level cross-source lineage.
**** Partial support: appears with Source Comments but is not fully completed.
Control Flow orchestration — the execution order and conditional logic defined by Precedence Constraints between tasks — is not represented in Alation. The connector catalogs the journey of the data itself, not the package’s execution logic.
How the Connector Models SSIS Objects¶
To provide an accurate view of data lineage, the connector translates SSIS components into a logical, table-based structure in the Alation catalog.
Package as Schema¶
The connector represents each SSIS package as a logical Schema. The schema name is constructed from the package’s deployment hierarchy to keep it unique:
Folder Name / Project Name / Package Name
Within this schema, all the package’s components — sources, destinations, transformations, and variables — are modeled as logical Tables.
Source, Virtual Source, and Destination Tables¶
For each source and destination, the following types of logical tables exist:
(SRC) table represents the actual source component (for example, a database table or a flat file). Its columns reflect the schema of the source itself.
(VSRC) virtual source table represents the data as it exists inside the SSIS data flow. It captures any column renaming (aliasing) that occurs within the SSIS source component. Its structure is often identical to the corresponding (SRC) table.
(DEST) table represents the actual destination component.
If the same source table is used in multiple Data Flow Tasks within one package, the connector appends a unique, deterministic ID in parentheses to the end of each logical table name (for example, (5653)) to distinguish between instances.
The high-level lineage flow is always from the source, through the virtual table, to the destination:
(SRC) Table → (VSRC) Virtual Source Table → (DEST) Table
A real package typically has several transformation components, such as Lookups or Derived Columns, between the virtual table and the final destination.
When the Alation connectors for the underlying source and destination systems (SQL Server or Oracle) are also configured and extracted, the (SRC) table links back to the actual source table in the catalog, and the (DEST) table links forward to the actual destination table, stitching together the full, end-to-end cross-system lineage. For the steps to enable this, see Configure Cross-Source Lineage.
Source Comments¶
The Source Comments field on each logical table and column shows the configuration of the original SSIS component, so you can understand what it does without opening the package in a development environment. Common fields include the SSIS component type and name, its description, its parent Data Flow Task name, and its connection manager or connection string. Component-specific fields are also captured — for example, the SQL command for an Execute SQL Task, the expression for a Derived Column, or the aggregation type for an Aggregate transformation.
Connection strings included in Source Comments are masked automatically: sensitive entries (for example, password) are replaced with a placeholder, while other entries such as host, database, and provider remain visible. No configuration is required for this masking.
Lineage for SSIS Variables¶
The connector models all discovered variables as columns in a special logical table named VARIABLES, which acts as a hub in the lineage graph. For example, when an Execute SQL Task populates a variable, the lineage shows data flowing from the task into the corresponding column of the VARIABLES table, and onward to any downstream component that uses that variable.
The connector performs static analysis of the package’s design. It can show that a variable is populated by a given task, but it cannot resolve the runtime value of that variable.
For the steps to run extraction and populate these objects, see Configure Metadata Extraction.