Documenting SQL Server Integration Services (SSIS)

19th July, 2022

SQL Server Integration Services (SSIS) is a Microsoft's ETL platform for building data transformations and data ingrations solutions. Dataedo supports documenting the SSIS packages contained in ISPAC deployment project files and DTSX package files. In future releases support for connecting to a SSIS server will be added.

Supported elements and metadata

Packages will be imported as procedures. 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.

Automatic data lineage

Before documenting SSIS project/package make sure that all of the data sources and destinations are imported as documentations in Dataedo. Furhtermore, the data source and destination should be created on package level. Project level connection managers support will be added in future releases. If all of the prerequisites are met, Dataedo will automatically create Data Flows for following tasks:

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

How to export an ISPAC project file

Connect to a SQL Server instance containing the SSIS project you want to document with SQL Server Management Studio. Then expand Integration Services Catalog, look for the project you want to document (on screenshot it's Daily ETL). Then right-click on the project and choose Export.

Export ispac

The windows explorer window will appear. Choose a location for the file and save.

Connect Dataedo to SQL Server Integration Services SSIS

To connect to SSIS create new documentation by clicking Add documentation and choosing Database connection.

Add connection

On the Add documentation window choose SQL Server Integration Services (SSIS):

add-documentation

On the next screen select Import Type:

  • ISPAC project deployment file - if you want to document all packages in a project file exported from SSIS SQL Server instance.
  • DTSX package file - if you want to document just one package, either extracted from ISPAC file, or created in Visual Studio.

For both mentioned options select the path of the appropriate file by clicking Browse button.

select-file

Click Connect. Dataedo will now read all the packages and will list them as Package type. Select packages to import and click Next. Set the Documentation Title and click Import.

Outcome

SSIS packages data has been imported to a new documentation. For tasks mentioned in Supported elements and metadata section, automatic data lineage was created.

outcome

Found issue with this article? Comment below
0
There are no comments. Click here to write the first comment.