Microsoft SQL Server Integration Services (SSIS) lineage configuration

To import lineage metadata from Microsoft SQL Server Integration Services, create a connection, data source definition and metadata import job.

Microsoft SQL Server Integration Services is a data integration platform.

Supported Microsoft SQL Server Integration Services versions

  • Microsoft SQL Server Integration Services 2022, SSIS v16 (full version 16.0.4240.4)
  • Microsoft SQL Server Integration Services versions from SSIS 2014 through the latest available releases are supported.

Prerequisite configuration

Before you import lineage metadata, ensure that the following prerequisites are met:

  • Server is accessible via TCP network
  • If you use the project deployment model:
    • Connect by using SQL Server authentication and have either the sysadmin role, the ssis_admin role, or all of the following privileges:
    • SELECT on tables/views:
      • SSISDB.catalog.projects
      • SSISDB.catalog.folders
      • SSISDB.catalog.packages
      • SSISDB.catalog.environment_references
    • EXECUTE on SSISDB.internal.get_project_internal
    • EXECUTE on SSISDB.catalog.get_parameter_values
    • READ on every extracted project
    • READ on every extracted project’s folder
  • If you use the package deployment model with packages deployed in SQL Server:
    • Connect by using SQL Server authentication and have either the db_ssisoperator role or all of the following privileges:
    • SELECT on msdb.dbo.sysssispackages
    • SELECT on msdb.dbo.sysssispackagefolders
  • Access to any additional configuration files stored on the server’s file system

Creating a metadata import asset

Data source connection

To connect to the data source from which you want to import lineage metadata, you need to select a data source definition and a connection. You can create them before you start creating the metadata import, or you can create them when you create and configure the metadata import asset.

Data source definition

Select Microsoft SQL Server as the data source type.

Connection

For connection details, see Microsoft SQL Server Integration Services connection.

Source

After you select a data source definition, in the Scanner field, select SSIS.

Connection mode

You can connect to Microsoft SQL Server Integration Services by using one of the following connection modes:

Include and exclude lists

You can include or exclude assets up to the package level. Provide folders, projects and packages in the format folder/project and folder/package. Each part is evaluated as a regular expression. Example values:

  • myFolder/.*: all projects and packages in myFolder folder,
  • myFolder/myProject: the myProject project in myFolder folder,
  • myFolder/mySubfolder/myPackage: the myPackage package in myFolder/mySubfolder folder

External inputs

If you use external Microsoft SQL Server Integration Services projects (in the .ispac format), packages (in the .dtsx format), and package configurations (in the .dtsconfig format), you can add them in a .zip file as an external input. The .zip file can have the following structure:

<SSIS_folder_name>
  [project_name].ispac
  [package_name].dtsx
  [package_config_name].dtsconfig
ssisVariableOverrides.csv
ssisComponentOverrides.csv

The ssisVariableOverrides.csv file contains variable overrides values. The ssisComponentOverrides.csv file contains component override values.

Advanced import options

Enable packages
You can extract packages that were deployed by using legacy Package Deployment Model.
Enable projects
You can extract projects that were deployed by using legacy Project Deployment Model.
Environment folder
You can specify the name of a folder of the environment that is used to set parameter values on the server. If no value is provided, the folder of the project is used.
Environment name
You can specify the name of the environment that is used to set parameter values on the server. If no value is provided, no environment is used and only literal values that are defined on the server are extracted.
Analyze data flow tasks
You can specify a comma-separated list of data flows to include in the analysis. As a value, specify the full name of the data flow task, represented by the PackagePath attribute in Microsoft SQL Server without the first backslash. Each value is evaluated as a regular expression. Example values:
  • Package\\Data Flow Task 1: The data flow task with the name 'Package\Data Flow Task 1`,
  • Package\\.*: All data flow tasks in the 'Package' package
Exclude data flow tasks
You can specify a comma-separated list of data flows to exclude from the analysis. As a value, specify the full name of the data flow task, represented by the PackagePath attribute in Microsoft SQL Server without the first backslash. Each value is evaluated as a regular expression. Example values:
  • Package\\Data Flow Task 1: The data flow task with the name 'Package\Data Flow Task 1`,
  • Package\\.*: All data flow tasks in the 'Package' package
Date format
You can specify the format of date and time. You can specify many formats, separated by the vertical bar (|), for example yyyy-MM-dd HH:mm:ss.S|HH:mm:ss. The values must comply with SimpleDateFormat format. To use the default date and time format, leave the value empty.
Current Date
You can specify a date to replace the value of the SSIS GETDATE() expression. Use this option if you do not want the lineage to be updated with the current date each time the analysis is run. Add the date in the format that you specified in the Date format setting. To use the current date, leave the value empty.
Current date UTC
You can specify a date to replace the value of the SSIS GETUTCDATE() expression. Use this option if you do not want the lineage to be updated with the current date each time the analysis is run. Add the date in the format that you specified in the Date format setting. To use the current date, leave the value empty.
Skip disabled tasks and packages
You can exclude disabled tasks and packages from the analysis. If you want to analyze them, set the value to false.

Learn more