SSIS Integration Requirements

The following are the prerequisites necessary for IBM Automatic Data Lineage to connect to this third-party system, which you may choose to do at your sole discretion. Note that while these are usually sufficient to connect to this third-party system, we cannot guarantee that the connection or integration will be successful since we have no control, liability, or responsibility for third-party products or services, including for their performance.

• SQL Server Integration Services: version 2012 or newer

• Server must be accessible via TCP

• If using the project deployment model:

  • User with SQL Server authentication and either the sysadmin role, ssis_admin role, or all 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 using the package deployment model with packages deployed in SQL Server:

  • User with SQL Server authentication and either the db_ssisoperator role or all 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

• Connection parameters:

  • Name or IP address of the SQL Server database where SSIS is deployed

  • Port on which the SQL Server database listens for JDBC connections

  • User name

  • Password

  • Automatic Data Lineage supports the following authentication types for MS SQL server

    SQL Server

    The user credentials are configured in the MS SQL server. SQL Server stores both the username and a hash of the password in the master database using internal authentication methods to verify login attempts. To use this type, fill in the username and password.

    Native (Windows Only)

    The identity of the user running Automatic Data Lineage is used to authorize access to the MS SQL server. MS SQL delegates the authentication to the Windows client where the user logged in.

    To use native authentication, the property integratedSecurity=true has to be set in the JDBC connection string. The Configurator adds this property automatically when the connection is saved.

    NTLM

    NTLM authentication allows you to authenticate a user against an active directory. To use NTLM authentication, you can, in addition to the username and password, fill in the domain property that identifies the domain controller.

    To use NTLM authentication, the properties authenticationScheme=NTLM;integratedSecurity=true have to be set in the JDBC connection string. Configurator adds those properties automatically when the connection is saved.

For more details about extraction via Manta Flow Agent, see Manta Flow Agent Configuration for Extraction.

Supported Features

Known Unsupported Features

Automatic Data Lineage does not support the following SSIS features. This list includes all of the features that IBM is aware are unsupported, but it might not be comprehensive.

• Third-party components are not supported in general. If these do not serve as connectors to the source/target system, the lineage is handled automatically via wise column mapping or can be overridden manually. (See SSIS Scanner Guide#Step-3:-Overriding-Component-Data-Lineage-(Optional) for more details.) However, connectors to external systems will result in a gap in lineage.

  • KingswaySoft components (CRM source and destination); for example:

    • KingswaySoft.IntegrationToolkit.DynamicsCrm.CrmSourceComponent

    • KingswaySoft.IntegrationToolkit.DynamicsCrm.CrmDestinationComponent

  • Task Factory components; for example:

    • PW.TaskFactory.DimensionMergeSCD

  • Attunity connectors — https://learn.microsoft.com/en-us/sql/integration-services/attunity-connectors?view=sql-server-ver16