--- updatedAt: 2026-06-12T17:55:46.000Z agentTools: projectIndex: https://docs.bigeye.com/llms.txt --- # Microsoft SSIS Connect Microsoft SQL Server Integration Services (SSIS) to Bigeye for lineage collection. 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](https://docs.bigeye.com/docs/lineage-plus-agent-1), which is installed and managed with the [Agent CLI](https://docs.bigeye.com/docs/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: ```sql use msdb; grant select on msdb.dbo.sysssispackagefolders to ; grant select on msdb.dbo.sysssispackages to ; ``` ### SSISDB catalog (project deployment) The simplest option is to grant the broad `ssis_admin` role: ```sql use SSISDB; exec sp_addrolemember 'ssis_admin', ``` > 🚧 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](#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](https://docs.bigeye.com/docs/installation), then run `./bigeye-agent install` and select `LINEAGE_PLUS`. See [Lineage Plus Agent](https://docs.bigeye.com/docs/lineage-plus-agent-1) 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](#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 | Key | Example | Description | | ------------------------------------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | **SSIS Database Connection** | | | | `sqlserverintegration.database.sqlserverversion.1` | `SQLSERVER2012` | The version of SSIS in use. Valid values: `SQLSERVER2008` (SSIS 2008), `SQLSERVER2012` (SSIS 2012), `SQLSERVER2014` (SSIS 2014), `SQLSERVER2016` (SSIS 2016 and 2017). | | `sqlserverintegration.database.url.1` | `jdbc:sqlserver://sqlserver2012:1433;databaseName=msdb` | The SSIS database server JDBC connection string. For Windows Authentication, leave username and password blank and use: `jdbc:sqlserver://:1433;IntegratedSecurity=true;databaseName=msdb`. | | `sqlserverintegration.packagetype.1` | `db` | Where packages are stored: `db` (database packages), `file` (file system packages), or `both`. | | `sqlserverintegration.database.username.1` | `sql_server_db_user` | Database username for the SQL Server database where packages are stored. The extract user should have read access on the catalog. | | `sqlserverintegration.database.password.1` | `sql_server_db_user_password` | The database user password for the SQL Server database. | | `sqlserverintegration.database.packagepaths.1` | `\\Test,\\Latest\\TestFolder,\\Test*,\\Test?` | Comma-separated list of SSIS database package paths; all packages under the specified paths are included in the snapshot. Paths are relative to the MSDB folder under Stored Packages in the SSIS Package Store. Wildcards: `?` (single character), `*` (multiple characters), `\\` to escape. Filters are **case-sensitive**. | | `sqlserverintegration.database.packagenames.1` | `package1,packDev*` | Comma-separated list of SSIS package names; only matching packages are included in the snapshot. If no filter is specified, all packages are included. Wildcards: `?` (single character), `*` (multiple characters), `\\` to escape. Filters are **case-sensitive**. | | `sqlserverintegration.filesystem.packagepaths.1` | `C:\\SSIS\\Projects\\Unit Test Project,C:\\SamplePackages` | Comma-separated list of SSIS file system package paths; all packages under the specified paths are included in the snapshot. | | `sqlserverintegration.filesystem.packagenames.1` | `package1,packDev*` | Comma-separated list of SSIS package names for file system packages; only matching packages are included. If no filter is specified, all packages are included. Wildcards: `?` (single character), `*` (multiple characters), `\\` to escape. Filters are **case-sensitive**. | | [`sqlserverintegration.temptablename.1`](sqlserverintegration.temptablename "sqlserverintegration.temptablename")​ | `#,tmp,temp` | Temp table patterns. | | `sqlserverintegration.decryptpackages.1` | `yes` | Whether to decrypt packages read from the file system: `yes` to decrypt, `no` to skip decryption. | | `sqlserverintegration.decryption.password.1` | `file_decrypt_password` | Password used to decrypt encrypted packages. | | `sqlserverintegration.exclude.hash.column.pattern.1` | `*scd2hashci,hash*` | Marks columns matching these naming patterns as excluded from the data flow graph. | | `snapshot.filtering.mode.1` | `singlewithinclude` | `singlewithinclude` is the only supported option at this time: a single repository file is created with all specified packages included. Include filters (`packagenames`/`packagepaths` parameters above) control which packages get included; if nothing is specified, the entire source system is created as one repository. | | **SSIS 2012 and 2014 Projects Deployed to SQL Server only** | | | | `sqlserverintegration.project.environment.references.1` | `"Integration Services Project2"=2;"Integration Services Project3"=3` | `"ProjectName"=ReferenceID` pairs, separated by `;`. The project name is the name in the SSISDB catalog; the reference ID comes from `[SSISDB].[internal].[environment_references]`. | | `sqlserverintegration.jdbc.driver.class.1` | `com.microsoft.sqlserver.jdbc.SQLServerDriver` | Name of the JDBC driver used for this connection. | | `sqlserverintegration.database.to.query.1` | `SSISDB` | `SSISDB` — for project deployment packages stored in the SSISDB catalog. | | `sqlserverintegration.database.url.1` | `jdbc:sqlserver://sqlserver2012:1433;integratedSecurity=true;databaseName=SSISDB` | The SSIS database server JDBC connection string for the SSISDB catalog. | | `sqlserverintegration.ssisdb.schema.name.1` | `catalog` | SSISDB schema name used for querying the SSISDB catalog. Default is `catalog`. | | `sqlserverintegration.ssisdb.table.schema.name.1` | `internal` | SSISDB internal schema name used for querying the SSISDB catalog. | | `sqlserverintegration.deployed.project.landingzone.1` | `C:\\metacenter_home\\resources\\SSIS\\SSIS2012\\DeployedProjects` | The landing zone directory where SQL Server 2012/2014 deployed projects are extracted. | | **Snapshot File Destination** | | | | `environment.name.1` | `DW_production` | Logical name to uniquely group projects in an SSIS environment; used as a key to group connections and parameters in the Connection and Parameter managers. | | `snapshot.output.1` | `metacenter.jcr#Snapshots//SQL Server Integration Services/` | The Content Repository path referenced by the snapshot. Replace the values in angle brackets; `` can be found in your `metacenter.xml` file. | ## 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`. ```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//SQL Server Integration Services/ ``` ## 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]`](https://learn.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-grant-permission-ssisdb-database) 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](https://docs.bigeye.com/docs/etl-connectors). * Contact Bigeye support or your Bigeye team if you need additional guidance for this connector.