MS SSIS Lineage Scanner

Scanned and supported objects

The scanner automatically generates lineage metadata for all currently supported sources and connections. However, lineage coverage might vary depending on system capabilities, dynamic transformations, or access permissions.

While we strive for comprehensive lineage capture, certain dataflows and transformations might be incomplete or unavailable due to technical constraints. We continuously work to expand coverage and accuracy.

The MS SSIS Lineage Scanner supports SQL Server Integration Services (SSIS) 2012 and later.

Scanned resources

The extracted metadata includes the following objects:

Folders
Projects
Packages and connection management files

Supported SSIS tasks

Dataflow Task

Supported components

CharacterMap
ConditionalSplit
DerivedColumn
FlatFileSource
Inserter
Lookup
ManagedComponentHost
Merge
MergeJoin
OLEDBDestination
OLEDBSource
RowCount
Sort
UnionAll

SQL queries extraction

SQL query extraction is supported only within the Data Flow Task component, and only for the following SSIS components:

OLEDBSource
OLEDBDestination
Lookup

Note:

Only single-statement SQL queries are supported for extraction.
Queries containing multiple statements (for example, using DECLARE before SELECT, or any batch execution) are not processed.
The EXEC statement is not supported.
The scanner builds SQL lineage without using any database dictionary metadata. Therefore, SQL statements that require access to the underlying database object definitions (tables or views) to resolve lineage are not supported and will not produce lineage.

Variable-based table definition limitations

When a variable is used instead of a literal table name, the variable value must be defined in one of the following formats:

[schema].[table]
'schema'.'table'
schema.table

Any other formats or dynamic concatenations of schema and table names are not supported for SQL extraction.

Currently, the scanner supports only variables that are defined within SSIS packages. Variables defined externally or through project parameters are currently not supported.

Variable-defined SQL queries

If an SQL query is defined through a variable that contains the full SQL statement (for example, when the component references a variable holding the query text), it is also supported for extraction. The scanner detects and parses SQL statements stored in variable definitions used by supported components.

Supported connectivity

Connector type: JDBC.
Authentication method: SQL Server Authentication using SQL login and password.

Supported connection types

The scanner currently supports the following SSIS connection types:

Connection type Description

Connection type	Description
`OLEDB`	OLE DB connections to MS SQL (other technologies do not provide any guarantee of data lineage quality).
`FLATFILE`	Connections to flat files such as `.csv` or `.txt` files.
`EXCEL`	Connections to Microsoft Excel workbooks.
`CACHE`	Connections to SSIS cache files used by components.
`ADO.NET:System.Data.SqlClient.SqlConnection`	Connections using ADO.NET with the `System.Data.SqlClient` provider (SQL Server).

OLEDB

OLE DB connections to MS SQL (other technologies do not provide any guarantee of data lineage quality).

FLATFILE

Connections to flat files such as .csv or .txt files.

EXCEL

Connections to Microsoft Excel workbooks.

CACHE

Connections to SSIS cache files used by components.

ADO.NET:System.Data.SqlClient.SqlConnection

Connections using ADO.NET with the System.Data.SqlClient provider (SQL Server).

Connection processing

The scanner identifies and categorizes connections by type to standardize lineage extraction and reduce duplication:

OLE DB / ADO.NET: Extracts host, database, and schema information.
Excel: Captures file paths and sheet names.
Flat File: Records file paths and basic structure (columns and delimiters).
Cache: Groups all cache connections under a unified logical connection.

Connections with identical or equivalent properties are consolidated automatically to prevent duplicates in the extracted metadata.

SSIS permissions

The user used for metadata extraction must have the following permissions:

SELECT on tables and views of the ssisdb.catalog schema.
EXECUTE on ssisdb.internal.get_project_internal.
READ on every extracted folder.
READ on every extracted folder’s project.
READ on every extracted project’s package.

Scanner configuration

All fields marked with an asterisk (*) are mandatory.

Property Description

Property	Description
`name*`	Unique name for the scanner job.
`sourceType*`	Specifies the source type to be scanned. Must contain `SSIS`.
`description*`	A human-readable description of the scan.
`oneConnections`	List of Ataccama ONE connection names for future automatic pairing.
`jdbcUrl*`	The full JDBC connection string used to connect to SQL Server.
`username*`	The SQL Server user.
`password*`	SQL server user password.

name*

Unique name for the scanner job.

sourceType*

Specifies the source type to be scanned. Must contain SSIS.

description*

A human-readable description of the scan.

oneConnections

List of Ataccama ONE connection names for future automatic pairing.

jdbcUrl*

The full JDBC connection string used to connect to SQL Server.

username*

The SQL Server user.

password*

SQL server user password.

SSIS scanner example configuration

{
   "scannerConfigs": [
      {
         "name": "SSIS Scan Connection",
         "sourceType": "SSIS",
         "description": "Scan SSIS project",
         "oneConnections": [],
         "jdbcUrl": "jdbc:sqlserver://mqlserver.database.windows.net;encrypt=true;trustServerCertificate=true",
         "username": "admin",
         "password": "@@ref:ata:[SSIS_USER_PASSWD]"
      }
   ]
}

Was this page useful?