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
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.
- SSIS Server - this option allows you to connect to a SQL Server instance holding an SSIS project.
For first two 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.
For SSIS Server option you will need to fill in connection details:
SSIS Server connection details
- Server name - SQL Server instance where SSIS is stored, e.g. localhost.
- Port - port under which SQL Server is available. In most cases you can leave default value of 1433
- Authentication - select authentication type for connection. These are same as authentication types for SQL Server. For selected authentication type you will need to fill in appropriate information:
- Windows Authentication - Dataedo will use your Windows account, hence no further details are needed
- SQL Server Authentication - user and password
- Azure Active Directory - Password - Azure AD user and password
- Azure Active Directory - Integrated - Azure AD login associated with Windows account
- Azure Active Directory - Universal with MFA - two-step authentication where you are redirected to Microsoft MFA login webpage.
- Database - select SSISDB (Catalog Database) from list or type its' name
- Folder - select Folder in which project is stored
- Project - select Project which is to be documented
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.
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.
