Skip to main content

About the SQL Server Integration Services (SSIS) collector

Important

This collector is available in Private Preview. If you would like access to this collector, please contact your Customer Success Director.

Use this collector to harvest metadata from SQL Server Integration Services which includes information on projects, packages, control flow and data flow elements, environment, environment variables, parameters, executions and operations.

Additionally, it gathers lineage information between control flow and data flow elements in a package and external data source such as SQL Server, Excel file, XML file or flat file (simple two-dimensional storage format to store data where the fields are usually separated by delimiters like commas (CSV), tabs (TSV), or spaces). The collector can collect metadata from both the project deployment model and the package deployment model.

ssis_sample_output.png

Note

The latest version of the Collector is 2.215. To view the release notes for this version and all previous versions, please go here.

What is cataloged

The collector catalogs the following information.

Table 1.

Object

Information cataloged

Folder

ID, Name, Description, Created By, Created At

Project

ID, Name, Description, Project Format Version, Deployed By, Deployed At, Created At, Version

Package

ID, Name, Description, Package Format Version, Minor Version, Major Version, Build Version, Version Comments, Is Entry Point, Created By

Control Flow Executable

ID, Name, Description, Type, SQL Statement

Data Flow Executable

ID, Name, Description, Type, Version

Data Flow Component

ID, Name, Description

Parameter

ID, Name, Description, Value, Type, Is Required, Is Sensitive

Environment

ID, Name, Description, Created By, Created At

Environment Variable

ID, Name, Description, Value, Type, Is Sensitive

Connection Manager

ID, Name, Type, Connection string, Retry Count, Retry Interval

Execution

ID, Created At, Started At, Stopped At, Started By, Stopped By, Machine Name, Server Name, Total Physical Memory, Available Physical Memory, Total Page Memory, Available Page Memory, Status

Operation

ID, Created, Operation Type, Status, Machine Name, Server Name, Started By, Started At, Stopped By, Stopped At

Column

ID, Name, Description, Is Sorted, Has Side Effects, Error Row Disposition, Truncation Row Disposition, Error or Truncation Operation, Column Type, Column Maximum Length



Relationships between objects

By default, the harvested metadata includes catalog pages for the following resource types. Each catalog page has a relationship to the other related resource types. If the metadata presentation for this data source has been customized with the help of the data.world Solutions team, you may see other resource pages and relationships.

Table 2.

Resource page

Relationship

Folder

  • Contains SSIS resource

Project

  • Contains Package

  • Uses Environment

  • Connects to Connection manager

  • Has parameter

Package

  • Contains Control Flow Executable

  • Connects to Connection manager

  • Has parameter

Control Flow Executable

  • Contains executable

  • Сontains Data Flow Executable

Data Flow Executable

  • Contains Data Flow Component

  • Accesses data from Data Source

Data Flow Component

  • Has Column

Parameter

  • References Environment Variable

Environment

  • Contains Environment Variable



Lineage for SQL Server Integration Services

Table 3.

Object

Lineage Available

SSIS Column

  • The column source when the source is a column in SQL Server, flat file, Excel file or XML file.

  • The column source when the source is a column in the same dataflow.

  • The column destination when the destination is a column in SQL Server, flat file, Excel file or XML file.



Authentication supported

  • The SQL Server Integration Services collector supports authentication via SQL Server Authentication. This requires the hostname, username, and password.

In this section: