SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) is Microsoft's ETL platform for building data transformations and data integration solutions. Dataedo supports documenting the SSIS packages contained in ISPAC deployment project files, DTSX package files, and connecting to an SSIS server.

Supported elements and metadata

Packages will be imported as Packages in Dataedo. Tasks within a package will be imported as data processes in Data Lineage, so that the data lineage can be easily extended with data flows.

Sources and Destinations types of objects from SSIS which are in Data Flow are imported into Dataedo as Sources and Destinations.

If the source of the data is an SQL query then Dataedo parses that query, and based on that parsing a lineage is created.

Connections that are in Connection manager in SSIS Package will be imported into Dataedo as Linked Sources.

Linked source contains information about the connection such as server, host, etc.

If Dataedo detects that the database to which the connection relates was previously documented, it will assign the matching database to the Linked source.

More about Linked Sources in this article

Dataedo imports project connections and package connections

Automatic data lineage

Before documenting SSIS project/package make sure that all of the data sources and destinations are imported as documentations in Dataedo. Furthermore, the data source and destination should be created on package level. If all of the prerequisites are met, Dataedo will automatically create Data Lineage for the following tasks (Control Flow):

• Data Flow Task (source and destination)
• Bulk Insert Task (destination)
• Execute SQL Task
• FTP Task

The data lineage for Sources and Destinations is also predicted when data sources are documented in Dataedo.

Automatic column data lineage

This type of lineage is provided for specific objects.

At the Process level in Dataedo, a column lineage is created when a process contains these objects:

• Balanced Data Distributor
• Conditional Split
• Lookup
• Multicast
• Cache Transform
• Export Column
• Percentage Sampling
• Aggregate
• Sort
• Union
• Pivot
• Row Sampling
• Unpivot
• Merge
• Merge Join

Column lineage, but not for columns newly created in the process:

• Data Conversion
• Derived Column Script Component
• Character Map
• Copy Column
• Fuzzy Grouping

At the Sources and Destinations object level in Dataedo, the column lineage is also available.

If you want to see the column lineage, the Columns level flows tag must be enabled.

Specification

Imported metadata

Dataedo reads the following metadata from SSIS.

	Imported	Editable
Packages	✅	✅
Control Flows as Processes	✅	✅
Column data lineage	✅	✅
Process description	✅	✅
Process script	✅	✅
Variables	✅
Script	✅
Description	✅	✅
Sources and Destinations	✅	✅
Data Lineage	✅	✅
Column data lineage	✅	✅
Process description	✅	✅
Process script	✅	✅
Script	✅
Description	✅	✅
Shared metadata
Dependencies	✅	✅
Created time	✅
Last updated time	✅