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

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:

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:

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:

Documenting SSIS packages

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

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

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

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)

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):

The Add integration service packages window will be shown:

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

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

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:

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:

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

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

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:

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:

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

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

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 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:

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

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 will be displayed on the right side of the main window:

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

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 files will be displayed on the right side of the main window:

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

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

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