SSIS

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: