Automating SSIS packages documentation

ApexSQL Doc is a tool that is used for SQL Server database documentation as well as SSIS packages, SSRS reports, SSAS cubes, Tableau sites and SharePoint farms. With ApexSQL Doc it’s possible to specify the exact server objects, attributes, database objects and specific object instances that can be generated in the documentation.

In this article, we are going to talk about how to document SSIS packages from five different data sources; file system, package store, SQL Server, SSISDB Catalog and SSIS Project file. Then we’ll cover automating the entire process by using PowerShell scripts.

Document packages from the File system

First of all, to be able to document SSIS packages, ApexSQL Doc must be running with administrator privileges on the operative system.

To document SSIS packages from the file system on your local client click on the Add SSIS package button in the Home menu or the Add button in the Integration services tab.

A dialog will appear and then select the Package file or File system option.

When it comes to selecting files from the file system needed for documentation, there are two possible methods. To select specific package files, click the Add button in the Add integration services packages dialog, browse and select the files separately. If there are multiple packages in the same folder, they can all be added at once using the Add folder button, navigate to the wanted folder location and click ok.

There is no specific limit of packages that can be documented because it depends on how large the package file is and what details need to be documented from it.

Document packages from the SSIS package store

To document packages from a package store, select the corresponding SSIS package store from the drop-down list. In the Server field, enter the name of the default SSIS instance and click the OK button.

Quick tip: It is possible to select between various SQL Server version, 2005 and higher. The package store files are the only packages that the Integration Services service manages.

Document packages from SQL Server

The third option of documenting is directly from SQL Server, either local or remote.

To document SSIS packages from SSISDB Catalog, select the corresponding option from Add SSIS packages menu, select the server where the SSISDB catalog is located and connect.

The fifth option is to document from SSIS project file. To do this, select the required option and navigate to the location of the desired SSIS project file using the browse button.

When the connection to a data source is established, a list of connected packages will appear in the section to the left and the list of connected servers will be visible in the section to the right.

Quick tip: It is possible to document packages from multiple locations whether local hosted or remote.

Select the desired packages that are needed for documentation and include/exclude details from the Package details tab.

With ApexSQL Doc it’s possible to include the following SSIS package details in the documentation:

• Configuration
• Connection managers – Include a full list of connection manager properties
• Control flow diagrams – Include graphically displayed control flow diagrams
• Data flow diagrams – Include graphically displayed data flow diagrams
• Event handlers – Include event handlers in the documentation
• Executables – A complete list of executable properties
• Extended properties
• Package source – Include the package source as .dtsx in documentation
• Log providers – Include log providers
• Precedence constraints – Include precedence constraints
• Project parameters
• System variables – Include a list of system variables
• Variables – Include list of variables, with names and description

After the selection is complete, save it all in a project file. To do this click the Save or Save as button in the Home menu.

And now you can Generate the documentation by simply clicking the Generate button.

Once the documentation plan is completed and tested, it is possible to automate the process of documentation. This example will be about documenting packages from the file system.

An example of a PowerShell script below will be shown for illustrative purposes and take in mind that all switches must be put in one line. In this example, we will use a saved project file and we will specify additional switches that will override additional settings if needed.

Create a new text document and enter the following lines.

The actual PowerShell script is shown below:

&’ApexSQLDoc.com’ /pr:”C:\Desktop\Project.axdp”
/isd:cfd dfd /ifk /of:chm /od:C:\Users\Alen\Documents\ /f /v
$x = $host.UI.RawUI.ReadKey(“NoEcho,IncludeKeyDown”)
$LASTEXITCODE

When that is done, save the file by putting a .ps1 extension and it is ready to run.

When creating a batch script, the format of the switches is the same but there are some differences between PowerShell and batch scripts. For creating a batch script see the example below:

@echo off
” ApexSQLDoc.com” /pr:”C:\Desktop\Project.axdp” /isd:cfd dfd /ifk /of:chm /od:C:\Users\Alen\Documents\ /f /v
echo “error code: ” %errorlevel%
pause

To save the file as a batch script, save the text file using a .bat extension.

After the documentation process is complete, the following output is shown:

Compiling in progress…

Compiling an HTML Help Project…
The documentation has been written in

C:\Users\Alen\Documents\ApexSQL\ApexSQLDoc\Packagedoc_05_06_2016.chm

To document specific packages from the SSIS package store, the /isps switch must be used.

Example:

/isps:services1:SQL2014.package1.package2:[password]

If some specific SQL Server packages are needed for documentation, use the /ssp switch.

Example:

/ssp:server1.user.password:package1.package2:[password]

It is also possible to use the scripts to execute the documentation of a previously saved project file.

Quick tip: To document packages from the file system, input the /pfs switch and specify the location of the wanted package files.

When running the automated process of documentation ApexSQL Doc automatically detects changes made in the package folders. So if there are some new packages added in the folder that is used for documentation, those files will be automatically detected and documented.

There is a possibility of getting some errors when documenting. For example, if the specified file is missing or maybe a wrong switch is used with an invalid argument. In this case ApexSQL Doc CLI uses return codes which may help in resolving the issue.

Examples of returned error messages:

“error code 1” – General error
“Couldn’t connect to SSIS local.”
“error code: ” 1

“error code 2” – Invalid switch format. In this case the /isps switch is misspelled or wrong switch specified.
“Switch ‘ispp’ is not recognized.”
“error code: ” 2

“error code 4” – A file or the directory already exists. To override previously created files specify the ”/f“ switch.
“The directory (local).Packagedoc_05_06_2016.chm already exists. Specify the /force switch to overwrite it”
“error code: ” 4

After the documentation process is complete, the result will be like shown in the image below.

It is also possible to change the style of the documentation using three predefined stylesheets or custom created styles.

Useful links:

• How to document SSIS package data flow
• How to document SSIS package control flow
• How to create SSIS package documentation
• Document SSIS packages and SQL databases using ApexSQL Doc CLI
• Documenting SSIS packages
• ApexSQL Doc and SSISDB catalog packages