Ataccama ONE Agentic ONE Desktop ONE DQ&C ONE MDM ONE RDM ONE Runtime Server
latest (17.2.0) 17.1.0 17.0.0 16.3.x 16.2.0 16.1.0 16.0.0 15.4.0 15.3.0 15.2.0 15.1.0 14.5.x 13.9.x 13.8.x 13.7.x 13.6.x 13.5.0 13.4.x 13.3.x 13.2.x
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 or, for standalone Docker installations, from local package files (see Local file-based scanner).

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

The scanner reads SSIS projects in one of two ways:

For the SSIS catalog:

  • 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

These permissions apply to the SSIS catalog only. They are not needed for the Local file-based scanner.

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

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

Property Description

sourceType*

Identifies the source type to be scanned. Must be set to SSIS.

description*

A human-readable description of the scan.

oneConnections

List of Ataccama ONE connection names for future automatic pairing.

connection*

Connection details for the SQL Server instance that hosts SSISDB. jdbcUrl, username, and password must be set inside this object; at the top level of the scanner configuration, they are rejected.

connection.jdbcUrl*

The JDBC connection string, for example jdbc:sqlserver://server_name:1433;encrypt=true;trustServerCertificate=false. For Azure SQL Database, use the full domain: server_name.database.windows.net.

The scanner always connects to the SSISDB database, so you don’t need to specify the database. If you include the database (databaseName or database), it must be SSISDB.

connection.username*

The SQL Server login.

connection.password*

The SQL Server login password.
SSIS scanner example configuration
{
   "scannerConfigs": [
      {
         "sourceType": "SSIS",
         "description": "Scan SSIS project",
         "oneConnections": [],
         "connection": {
            "jdbcUrl": "jdbc:sqlserver://server_name.database.windows.net:1433;encrypt=true;trustServerCertificate=false",
            "username": "admin",
            "password": "@@ref:ata:[SSIS_USER_PASSWD]"
         }
      }
   ]
}

Local file-based scanner

As an alternative to reading projects from the SSIS catalog, the scanner can read SSIS package files directly from a local directory. This is useful when you have the project files available, or when network access to the SQL Server instance hosting SSISDB is not available.

Before you begin

Before running the scan, place the SSIS files into a directory under <install>/user-data/, where <install> is the path where the standalone Docker scanner is installed (see Standalone Lineage Scanner). The scanner expects the same folder and project hierarchy as in the SSIS catalog:

Expected directory structure
<install>/user-data/ssis_dir/
└── <folder>/
    └── <project>/
        ├── <package>.dtsx
        └── <connection_manager>.conmgr

Each subdirectory of the root directory is treated as an SSIS folder and each of its subdirectories as a project. In each project directory, the scanner reads the package (.dtsx) and project connection manager (.conmgr) files and ignores anything deeper.

The scanner does not read .ispac files. An .ispac file is a ZIP archive, so extract its .dtsx and .conmgr files into the project directory first.

Local file-based scanner configuration

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

Property Description

sourceType*

Identifies the source type to be scanned. Must be set to SSIS.

description*

A human-readable description of the scan.

oneConnections

List of Ataccama ONE connection names for future automatic pairing.

folderPath*

Path to the root directory containing the SSIS folders, as seen from inside the scanner container.

The <install>/user-data/ directory on the host is mapped to /opt/ataccama/lineage-scanning/user-data/ inside the container. For example, if you placed your files in <install>/user-data/ssis_dir, set this to /opt/ataccama/lineage-scanning/user-data/ssis_dir.

connection*

Not used when folderPath is set, but must be present with jdbcUrl, username, and password set to empty strings, as in the following example.
Local file-based scanner example configuration
{
   "scannerConfigs": [
      {
         "sourceType": "SSIS",
         "description": "Scan SSIS projects from local files",
         "oneConnections": [],
         "folderPath": "/opt/ataccama/lineage-scanning/user-data/ssis_dir",
         "connection": {
            "jdbcUrl": "",
            "username": "",
            "password": ""
         }
      }
   ]
}

Was this page useful?