Connectivity Summary
An out of the box connector is available for SSIS. It provides support for crawling datasets i.e. SSIS Folders, Projects, Packages and lineage building.
The connectivity to SSIS is via JDBC, which is included in the platform.
Connector Capabilities
The connector capabilities are shown below:
Crawling
Supported objects for Crawling are given below:
| Supported Objects | Remarks |
| Jobs | It fetches all Projects and Packages from SSISDB (Catalog DB) |
Lineage Building
| Lineage Entities | Details |
|
Table - Table Lineage |
Supported |
|
Table - File Lineage |
Supported |
|
File - Table Lineage |
Supported |
|
Column Lineage - File Column Lineage |
Supported |
Querying
|
Operation |
Details |
|
Select |
Supported |
|
Insert |
Not supported, by default. |
|
Update |
Not supported, by default. |
|
Delete |
Not supported, by default. |
|
Joins within database |
Supported |
|
Joins outside database |
Not supported |
|
Aggregations |
Supported |
|
Group By |
Supported |
Pre-requisites
To use the connector, the following need to be available:
- Connection details as specified in the following section should be available.
- A Service account, for crawling . The minimum privileges required are:
| Operation | Access Permission |
|
Connection validate |
Should have permission for the specified path |
Connection Details
The following connection settings should be added for connecting to an SSIS:
- Database Type: SSIS
- License Type: Standard or Auto Lineage
- Connection Name: Select a Connection name for the SSIS. The name that you specify is a reference name to easily identify the SSIS connection in OvalEdge.
Example: ssis1 - Server: IP Address of SSIS
- Port: Provide the valid port
- Database: Provide the valid database name
- Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
- Username: Provide the valid username
- Password: Provide valid password
- Connection String: jdbc:sqlserver://{server}:1433;database={sid}
SSIS requires SSIS db username, db Password, Machine username, Machine password, local path to store package files . All the fields are mandatory. The connection will be successful only if connection details are correct and a valid Local Path.
Additional Information
Scripts on powershell:
- Enable-PSRemoting -Force
- Enable-PSRemoting -Force -- Enable Firewall for remote access
- Restart-Service WinRM
- winrm quickconfig
- Get-Item WSMan:\localhost\Client\TrustedHosts
- Set-Item WSMan:\localhost\Client\TrustedHosts -Value *
- Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Unrestricted
- Adding new fields, sys login username,password, local file path (to copy package files).
- Default download location of downloading files in machine - C:\ssis_download_folder\download
- Run ‘Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Unrestricted’ via powershell
- Last check (not needed) - Wifi network - properties - private/public.
Points to be noted
- Before to build Lineage, connections need to be crawled with options “Crawl/Profile” of the connection.
- Alternate Option to crawl SSIS is using Advance job: "Load SSIS Folders, Projects and Packages in to Ovaledge". Attributes are given as shown below:
