How to document SQL Server Integration Services (SSIS) using ApexSQL Doc

Applies to

ApexSQL Doc


This article explains how to document SSIS packages using the SQL Database documentation tool.


In the beginning, we will go through the basics and requirements for documenting SSIS packages.

Install SQL Server and SQL Server Integration Service

The first thing to do, before documenting SSIS packages, is to install SQL Server and SQL Server Integration Services locally, on the same machine where ApexSQL Doc is installed. If SQL Server and SQL Server Integration Services are not installed, ApexSQL Doc will throw the following message:

Documenting SSIS packages

However, if documentation is generated without SQL Server and SQL Server Integration Services, the output will be generated empty. This is an example of empty documentation:

Empty SSIS documentation

To verify that SQL Server Integration Services is installed and running, go to Services (Start | Run | Services.msc from Command Prompt), locate SQL Server Integration Services and verify that its status is Running as shown below:

SSIS must be installed

Documenting SSIS packages

For documenting SSIS packages using ApexSQL Doc, make sure to run the program as an administrator:

Run as administrator

Otherwise, the below message will be shown when trying to add SSIS packages store:

The ApexSQL Doc must run as administrator

Using the SQL Database documentation tool various SSIS packages can be documented. ApexSQL Doc provides a possibility to document sources like:

  • Package files or file system
  • SSIS package store (SQL Server)
  • SQL Server
  • SSISDB catalog
  • SSIS project file

The details that will be included in the documentation can be adjusted in the Package details section:

  • Configuration
  • Connection manager – A list of connection manager’s properties and elements, including a connection string, creation name, delay validation, description, host type, ID, InitialCatalog, name, protection level, server name, user name, and RetainSameConnection status
  • Control flow diagrams – Include control flow diagrams in documentation
  • Event handlers – Include event handlers in the documentation
  • Executables – A list of executables properties, including CreationName, DebugMode, DelayValidation, Description, Disable, DisableEventHandlers, ExecutionDuration, ExecutionResult, ExecutionStatus, FailPackageOnFailure, FailParentOnFailure, ForcedExecutionValue, ForceExecutionResult, ForceExecutionValue,ID, IsDefaultLocaleID, IsolationLevel, LocaleID, LoggingMode,MaximumErrorCount, Name, SuspendRequired, TransactionOption
    • Data flow diagrams (Currently the last SSIS Server that we support for data flow diagram documentation is 2014)
  • Extend properties
  • Include package source (Include package source as .dtsx in documentation)
  • Log providers – Include log providers in the documentation
  • Log entry information
  • Precedence constraints – Include precedence constraints in the documentation
  • Project parameters
  • System variables
  • Variables – A list of variables, including name and description

Packages details settings in the SQL database documentation tool

The format of the documentation can be selected in the File format step from the Output options section. The supported formats to document packages are:

  • CHM (Compiled help .chm)
  • Linked HTML (.html)
  • Markdown (.md)
  • DOC (Word document 97 – 2003)
  • DOCX (Word document 2007 – 2013)
  • Portable Document Format (.pdf)

Output file formats to generate SSIS packages using the SQL database documentation tool

Documenting Package files

To document SSIS packages from the project file, chose Integration services from the Data sources and objects panel, then click on the Add button. (The same process can be done by clicking on the Add SSIS package from the Home tab in the Actions section):

Add SSIS packages into ApexSQL Doc

The Add integration service packages window will be shown:

Add integration service packages

To add one or more packages from the folder, click on the Add button and choose packages for the documentation:

Select SSIS packages for documenting

Only selected packages will be shown in the Add integration service packages window:

Selected SSIS packages will be added

Also, the whole folder with SSIS packages can be added by click on the Add folder button.

The Remove button is used to remove one or more SSIS packages from the list:

Remove Selected SSIS packages

All SSIS packages that are chosen, will be displayed in the Packages section on the right side of the main window. All selected SSIS packages from the list will be documented when the Generate button is clicked from the Actions menu:

Document SSIS packages using SQL database documentation tool

The Save as dialog will be open. Choose a destination for the documentation. Then click the Save button:

Save as generated documentation

The generating process will start. It can be stopped at any time by clicking the Stop button:

Generating the documentation process of the SSIS packages

When the process of generating the documentation is finished, the document can be open immediately. This is an example of how documentation looks using the .chm format:

An example of how documentation of SSIS packages looks using SQL database documentation

Documenting SSIS package store

The process is the same as above, the only difference is that from the Location drop-down menu, SSIS package store (SQL Server) should be chosen along with the desired SQL Server:

Document SSIS package store

The SSIS packages (in this case from SQL Server 2019) will be displayed on the right side of the main window:

List of SSIS package store in the ApexSQL Doc

Chosen SSIS packages will be documented after the Generate button is clicked and below is an example using the .chm format:

SSIS packages documentation

Documenting SQL Server

To document SQL Server, from the Location drop-down menu choose SQL Server, select the targeted Server and choose the Authentication type.

The authentication can be done by:

  • Windows
  • SQL Server
  • Active Directory – Password
  • Active Directory – Integrated
  • Active Directory – Universal with MFA support

The list of authentication type in the ApexSQL Doc

The SQL Server and SSIS packages will be loaded in the main window and documentation can be created. The difference between the SSIS package store and SQL Server is the location of the stored packages. The packages can be stored in an instance of Microsoft SQL Server or to a folder in the SSIS package store:

SSIS package store

This is an example of documented SQL Server using the .chm format:

Generated documentation of the SSIS package store

Documenting SSISDB catalog

For a documenting SSISDB catalog from the Location drop-down menu choose SSISDB catalog, select the wanted Server and choose the Authentication type (types of authentication are the same as in the previous case):

SSISDB catalog

SSISDB catalog will be displayed on the right side of the main window:

SSISDB catalog can be documented using the SQL database documentation tool

This is an example of a documented SSISDB catalog using the .chm format:

Example of the generated documentation of the SSISDB catalog

Documenting SSIS project file

To document the SSIS project file from the Location drop-down list select SSIS project file and choose Path of SSIS project file:

SSIS project file

SSIS project files will be displayed on the right side of the main window:

List of the SSIS project files in the ApexSQL Doc

This is an example of SSIS project files documentation using the .chm format:

Example of the generated documentation of the SSIS project files using SQL documenting tool

All the previous examples that we have documented so far can be documented at once:

SSIS packages can be documented using the SQL database documentation tool

This is an example of all packages documented at the same time in one document:

Example of SSIS documentation,  generated by ApexSQL Doc