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.
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.
On the Add documentation window choose SQL Server Integration Services (SSIS):
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.
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.
SSIS packages data has been imported to a new documentation. For tasks mentioned in Supported elements and metadata section, automatic data lineage was created.