Ataccama ONE Agentic ONE Desktop ONE DQ&C ONE MDM ONE RDM ONE Runtime Server
Downloads

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 Microsoft SQL Server Integration Services (SSIS) 2012 and later and projects deployed with the project deployment model. It reads the projects from the SSIS catalog (SSISDB) on Microsoft SQL Server or Azure SQL Database. Scanning package or project files directly, such as .dtsx or .ispac files, is not supported.

Only the built-in Microsoft components listed on this page are supported. Third-party and custom components, such as custom .NET components, do not produce lineage.

Scanned resources

The extracted metadata includes the following objects:

  • Folders

  • Projects

  • Packages

  • Project and package connection managers

Supported SSIS tasks

  • Data Flow Task, including Data Flow Tasks nested in containers such as Sequence, For Loop, and Foreach Loop containers.

Other control flow tasks, including the Execute SQL Task, do not produce lineage.

Supported components

The scanner supports the following built-in components:

Sources:

  • ODBC Source

  • OLE DB Source

  • Excel Source

  • Flat File Source

Transformations:

  • Aggregate

  • Cache Transform

  • Character Map

  • Conditional Split

  • Data Conversion

  • Derived Column

  • Lookup

  • Merge

  • Merge Join

  • Multicast

  • Row Count

  • Sort

  • Union All

Destinations:

  • ODBC Destination

  • OLE DB Destination

  • Excel Destination

  • Flat File Destination

SQL queries extraction

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

  • OLE DB Source, when the data access mode is SQL command or SQL command from variable.

  • OLE DB Destination, when the data access mode is SQL command.

  • ODBC Source, when the data access mode is SQL command.

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.

Table name limitations

Table names must use two-part names (schema and table) in one of the following formats:

  • [schema].[table]

  • "schema"."table"

  • schema.table

Three-part names that include the database (for example, database.schema.table) and dynamic concatenations of schema and table names are not resolved to tables.

Variables

The scanner resolves variables referenced by supported components in the following cases:

  • Table name variables: The variable holds a table name. The value must follow the formats described in Table name limitations.

  • SQL query variables: The variable holds a full SQL statement. The statement is parsed the same way as an SQL command, so the limitations in SQL queries extraction apply.

The scanner supports only variables scoped to the package. Variables scoped to a container or task, variables defined externally, and project parameters are currently not supported.

Supported connectivity

  • Connector type: JDBC, to the SQL Server instance that hosts the SSISDB database.

  • 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

OLEDB

OLE DB connections to Microsoft SQL Server and Oracle (Oracle Provider for OLE DB and Microsoft OLE DB Provider for Oracle). Connections using other OLE DB providers are treated as SQL Server, so lineage quality is not guaranteed.

ODBC

ODBC connections. The technology is detected from the driver: SQL Server, Oracle, PostgreSQL, Snowflake, Teradata, and Google BigQuery are recognized. Connections using other drivers are treated as a generic database.

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, ODBC, and ADO.NET: Extracts host, database, and schema information. For Oracle, the host and service name or SID are read from TNS descriptors and Easy Connect strings.

  • 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 in the SSISDB database:

  • SELECT on the catalog.folders, catalog.projects, and catalog.packages views.

  • EXECUTE on the internal.get_project_internal stored procedure.

  • READ on every extracted folder.

  • READ on every extracted folder’s project.

  • READ on every extracted project’s package.

Scanner configuration

Connection details and credentials are inherited from the source connection. See MS SSIS Connection.

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

Property Description

description*

A human-readable description of the scan.

oneConnections

List of Ataccama ONE connection names for future automatic pairing.
SSIS scanner example configuration
{
   "scannerConfigs": [
      {
         "description": "Scan SSIS project",
         "oneConnections": [],
         "connection": {}
      }
   ]
}

Was this page useful?