Microsoft SSIS

The Microsoft SQL Server Integration Services (SSIS) Connector ingests package metadata to build end-to-end lineage across your ETL ecosystem.

Supported Features

• Extraction of SSIS package metadata from the MSDB database (folder-based package store), the SSISDB catalog (project deployment model), and the file system
• Decryption of encrypted file-system packages
• Include filtering by package path and package name, with wildcard support
• Enrichment of connections and parameters via the Connection and Parameter managers
• Project environment references for SSIS 2012/2014 projects deployed to SQL Server

Supported SSIS versions: 2008, 2012, 2014, 2016, 2017, 2019

How It Works

The connector extracts metadata from SSIS to an XML file, which is then loaded into Lineage Plus. The connector runs through the Lineage Plus agent, which is installed and managed with the Agent CLI and can run on-demand or on a schedule.

SSIS packages are stored in two formats:

• MSDB database: packages (.dtsx files) are stored in a folder structure.
• SSISDB catalog: packages use a project format with references to parameters, environment variables, and other packages.

The connector can extract from either store (or the file system), depending on the sqlserverintegration.packagetype.1 setting.

Permissions

The snapshot user needs access to the msdb and SSISDB catalogs. With SQL Server 2012 and later, packages stored in the MSDB database require Windows Authentication for the snapshot user.

MSDB-stored packages

Grant the snapshot user the following permissions:

use msdb;
grant select on msdb.dbo.sysssispackagefolders to <user>;
grant select on msdb.dbo.sysssispackages to <user>;

SSISDB catalog (project deployment)

The simplest option is to grant the broad ssis_admin role:

use SSISDB;
exec sp_addrolemember 'ssis_admin', <user_name>

🚧

The ssis_admin role grants wide-ranging abilities. If assigning this role is restricted in your environment, assign granular read-only permissions instead — see Assigning Granular SSISDB Permissions below.

Setup

The SSIS connector runs through the Lineage Plus agent and is configured with the Agent CLI.

Steps:

1. Install the Lineage Plus agent

   • Download and install the Agent CLI, then run ./bigeye-agent install and select LINEAGE_PLUS. See Lineage Plus Agent for host requirements and prerequisites.

2. Add the SSIS connector

   • Bash

     ./bigeye-agent add-connector -c ssis

   • The CLI walks you through the connector configuration interactively. See Configuration Parameters for details on each setting.

3. Run lineage collection

   • Bash

     ./bigeye-agent lineage run -c ssis

   • If the connector runs successfully, you'll see a success message. Allow several hours for lineage data to sync into Bigeye.

4. Provide connection and parameter information

   • Once the lineage data is loaded, use the Connection Manager to specify how each SSIS connection maps to database models.
   • After the connection information is provided, rerun lineage collection for data lineage to be calculated.

Configuration Parameters

Sample Properties File

If you have an existing properties file like the one below, it can be imported with ./bigeye-agent add-connector -c ssis --from-existing /path/to/sqlserverintegration.properties.

# ===== SSIS database connection =====
sqlserverintegration.database.sqlserverversion.1=SQLSERVER2012
sqlserverintegration.database.url.1=jdbc:sqlserver://sqlserver2012:1433;databaseName=msdb
sqlserverintegration.database.username.1=sql_server_db_user
sqlserverintegration.database.password.1=${SSIS_DB_PASSWORD}
# For Windows Authentication, leave username/password blank and use:
# sqlserverintegration.database.url.1=jdbc:sqlserver://sqlserver2012:1433;IntegratedSecurity=true;databaseName=msdb

# ===== Package source =====
# db = database packages, file = file system packages, both = db and file system
sqlserverintegration.packagetype.1=db
sqlserverintegration.database.packagepaths.1=\\Test,\\Latest\\TestFolder
sqlserverintegration.database.packagenames.1=package1,packDev*
# sqlserverintegration.filesystem.packagepaths.1=C:\\SSIS\\Projects\\Unit Test Project,C:\\SamplePackages
# sqlserverintegration.filesystem.packagenames.1=package1,packDev*

# ===== Package decryption (file system packages) =====
sqlserverintegration.decryptpackages.1=yes
sqlserverintegration.decryption.password.1=${SSIS_DECRYPT_PASSWORD}

# ===== Optional tuning =====
sqlserverintegration.exclude.hash.column.pattern.1=*scd2hashci,hash*

# ===== SSIS 2012/2014 projects deployed to SQL Server only =====
sqlserverintegration.project.environment.references.1="Integration Services Project2"=2;"Integration Services Project3"=3
sqlserverintegration.jdbc.driver.class.1=com.microsoft.sqlserver.jdbc.SQLServerDriver
sqlserverintegration.database.to.query.1=SSISDB
sqlserverintegration.database.url.1=jdbc:sqlserver://sqlserver2012:1433;integratedSecurity=true;databaseName=SSISDB
sqlserverintegration.ssisdb.schema.name.1=catalog
sqlserverintegration.ssisdb.table.schema.name.1=internal
sqlserverintegration.deployed.project.landingzone.1=C:\\metacenter_home\\resources\\SSIS\\SSIS2012\\DeployedProjects

# ===== Output & strategy =====
snapshot.filtering.mode.1=singlewithinclude
environment.name.1=DW_production
snapshot.output.1=metacenter.jcr#Snapshots/<metacenter-instance-id>/SQL Server Integration Services/<repository-environment-name>

Assigning Granular SSISDB Permissions

If granting the ssis_admin role is restricted in your environment, assign read-only permissions using SQL Server Management Studio (SSMS) or T-SQL scripting.

Using SQL Server Management Studio

1. Log in to the SQL Server using SSMS with DBA privileges using Windows Authentication, then grant the user read access to internal.folders and internal.projects:

   SQL

   use ssisdb;
   exec sp_grantdbaccess sqlserver_unit_test
   grant select on internal.folders to sqlserver_unit_test
   grant select on internal.projects to sqlserver_unit_test

2. Expand Security.

3. Double-click the user configured to use SQL Server Authentication.

4. Click SSISDB in Users Mapped to this Login.

5. Click public to add the database role membership for SSISDB.

6. Expand Integration Services Catalogs.

7. Expand SSISDB.

8. Right-click the project folder to assign permissions to the user.

9. The Folder Properties window opens.

10. Click Permissions.

11. Click Browse.

12. Select the SQL user to map the permissions to.

13. Click OK.

14. In the Permissions list, check the boxes to grant Read and Read Objects.

Repeat these steps for all top-level project folders. Assigning the privileges at the root folders cascades the privileges to the projects underneath them.

Using T-SQL Scripting

📘

These permissions can only be assigned by logging in as a Windows Authenticated user.

1. Grant the SQL user access to the database:

   SQL

   use ssisdb;
   exec sp_grantdbaccess sqlserver_unit_test
   grant select on internal.folders to sqlserver_unit_test
   grant select on internal.projects to sqlserver_unit_test

2. List the folders that require access:

   SQL

   use ssisdb;
   select * from internal.folders

3. Assign folder permissions by invoking the [SSISDB].[catalog].[grant_permission] stored procedure:

   SQL

   grant_permission [ @object_type = ] object_type
       , [ @object_id = ] object_id
       , [ @principal_id = ] principal_id
       , [ @permission_type = ] permission_type

   • @object_type — the type of securable object: folder (1), project (2), environment (3), or operation (4).
   • @object_id — the unique ID of the securable object (from the result set in step 2).
   • @principal_id — the ID of the principal to be granted permission. Look it up with: select principal_id from sys.database_principals where name='sqlserver_unit_test'
   • @permission_type — the type of permission: 1 (Read), 101 (Read Objects).

4. Grant Read and Read Objects on each project folder:

   SQL

   --Assign (Read) and (Read Objects) to sqlserver_unit_test for Project2 Folder
   EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=1, @principal_id=10, @permission_type=1
   GO
   EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=1, @principal_id=10, @permission_type=101
   GO
   
   --Assign (Read) and (Read Objects) to sqlserver_unit_test for Project3 Folder
   EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=2, @principal_id=10, @permission_type=1
   GO
   EXEC [SSISDB].[catalog].[grant_permission] @object_type=1, @object_id=2, @principal_id=10, @permission_type=101
   GO

Operating Notes

• Case sensitivity: all package path and package name filters are case-sensitive. Wildcards: ? (single character), * (multiple characters), escape with \\.
• Logs and diagnostics: view run logs with ./bigeye-agent lineage logs -c ssis, or share diagnostics with Bigeye support via ./bigeye-agent lineage share-diagnostics -c ssis.

What's Next

• Browse other supported ETL lineage connectors in ETL Connectors.
• Contact Bigeye support or your Bigeye team if you need additional guidance for this connector.