Configure Cross-Source Lineage

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.

If you’ve also configured the Alation connectors for the SSIS package’s source and destination systems (SQL Server or Oracle), the SSIS connector can stitch together the full, end-to-end lineage: the logical (SRC) table links back to the actual source table in the catalog, and the (DEST) table links forward to the actual destination table. For details on these logical tables, see Overview.

Based on the type of data source, use one of the following ways to configure cross-source lineage:

Note

Before proceeding with further tasks, ensure that you have installed the OCF connector for the required data source (SQL Server, Oracle, or both) and successfully run MDE for the relevant database assets. See Prerequisites.

Configure Automatic Cross-Source Lineage (SQL Server Only)

Enabling automatic cross-source lineage for SQL Server is a two-stage process: run MDE on the SSIS connector to extract the connection details, configure the SQL Server connector with those details, then run SSIS MDE a second time to build the final lineage links.

Step 1: Perform the Initial SSIS MDE Run

Run a full metadata extraction on your newly configured SSIS connector. This initial run does not show cross-source lineage, but it populates the Alation catalog with the logical SSIS tables and their connection metadata. For the extraction steps, see Configure Metadata Extraction.

Note

If you already ran a full extraction while configuring MDE, that run satisfies this step. Continue to Step 2.

Step 2: Find the Server Name from the SSIS Metadata

Once the first MDE run completes, find the exact server name that your SSIS packages use:

  1. Navigate to the logical schema representing one of your SSIS packages in the Alation catalog.

  2. Open one of the (SRC) or (DEST) logical tables that correspond to the SQL Server database you want to link.

  3. In the Source Comments section, find the CONNECTION STRING property.

  4. Copy the server address from the Data Source= portion and the database name from the Initial Catalog= portion of the string.

Step 3: Configure the Alation SQL Server Connector

Provide the server name from Step 2 as an alias in your SQL Server connector’s settings:

  1. Navigate to the corresponding SQL Server data source in Alation.

  2. Go to Settings and scroll down to the Advanced settings (optional) section.

  3. In the Additional data source connections field, paste the server name and catalog name you copied from the SSIS connection string.

  4. Append :-1 to the end of the server name. This format is required for the matching to work.

    • Example: your-sql-server-name~|~your-catalog-name:-1

  5. If the SQL Server data source contains other catalog (database) names that SSIS packages reference, add them here, separated by commas, using the same SQL Server name.

  6. Click Save.

Step 4: Perform the Final SSIS MDE Run

Navigate back to your SSIS connector and run its metadata extraction a second time. With the SQL Server connector now configured with the correct alias, this MDE run stitches everything together and generates the full, end-to-end lineage between your SQL Server tables and the SSIS package.

Note

The alias in Step 3 must be saved before you run this final MDE. If you add or change the alias after MDE has already run, you must run SSIS MDE again.

Configure Manual Cross-Source Lineage (SQL Server or Oracle)

You can also manually configure cross-data source lineage between your SSIS packages and specific Oracle or SQL Server data sources.

Important

For SQL Server, manual configuration and automatic discovery are mutually exclusive. If you manually configure a SQL Server data source using the steps below, the automatic lineage process described above does not run, even if you already completed those steps.

How to Identify Your Data Source

The fields in this section require a unique identifier for your existing Oracle or SQL Server data source. Use one of the following:

  • Alation Data Source ID: The numerical ID for your data source in Alation, found in the URL of the data source’s homepage. For example, in the URL .../data/123/..., the ID is 123.

  • Host and Port: The host:port pair already configured in the Additional data source connections field on that data source’s settings page.

Configure Lineage

To configure cross-data source lineage, perform these steps:

  1. Navigate to the Settings page for your SSIS connector.

  2. Expand the configuration section for the data source type you’re linking: Configure Oracle Cross Data Source Lineage or Configure SQL Server Cross Data Source Lineage. Oracle and SQL Server each have their own section, but the fields and steps below are the same for both.

  3. In the Source… field, enter the Alation Data Source ID or the host:port pair for the data source that acts as a source in your SSIS packages.

  4. In the Destination… field, enter the identifier for the data source that acts as a destination.

  5. Click Save.

After you save and run MDE on the SSIS connector, Alation creates lineage paths to and from the data sources you specified.

Limitations

  • You can specify only one source data source and one destination data source per type (one for Oracle, one for SQL Server).

  • This single configuration applies to all packages ingested by this SSIS data source.

This manual option works best when all packages cataloged in Alation share a common source and destination pattern. If your SSIS project has packages with varied source and destination patterns (for example, some move data from Oracle to SQL Server, while others move it from SQL Server to Oracle), create multiple SSIS data sources in Alation and use Select Packages for Extraction on each one to ingest only the packages that match its manual lineage configuration.