How to document SSIS package control flow

Control flow is the SQL Server workflow engine that contains control flow elements. An SSIS package consists of at least one control flow task, and optionally one or more data flows.

There are three types of control flow elements:

• Containers – provide structure to package and services tasks. They support repeating control flow tasks, and grouping into meaningful units. A single container can be created inside another container, along with additional tasks. Depending on the type, it could be used to repeat tasks for each element in a collection, repeat tasks until the specified condition is met, or the container can group tasks and other containers in units that must succeed or fail when finished.

• Control flow tasks – workflow objects that perform a high level of operations, such as sending an email message, executing a SQL statement, or copying file from a FTP server. If the package contains more than one control flow task, they are connected and sequenced with a precedence constraint. When the control flow task is finished, it either succeeds or fails.

• Precedence constraints – connect tasks, executables, and containers inside the control flow, and specify a condition that determines whether the task will run or not. Precedence constraints can be configured by logical AND or logical OR expressions, and succeed or fail. One task can be connected to another with multiple precedence constraints, and for each constraint, a separate condition can be specified

Similar to documenting data flow, documenting a control flow task means to define a descriptive name for the task, add more about the task in the Description field, or to add an annotation. When this information needs to be accessed, the SSIS package needs to be opened. This could be a problem if the package contains numerous control flow tasks with multiple data flow tasks.

ApexSQL Doc is a documenting tool for SSIS packages,SSRS items, SSAS cubes and SQL databases, Tableau sites and SharePoint farms that generates user-friendly documentation in various formats: CHM, HTML, DOC, DOCX, and PDF.

Documenting SSIS packages includes properties for each SSIS task, both control flow and data flow diagrams. SSIS package sources supported by ApexSQL Doc are SQL Server, file system, and SSIS Package Store. The documenting process can be specified using the Command line Interface and scheduled to run unattended.

To document a SSIS control flow diagram:

1. Start ApexSQL Doc as an administrator

2. Click the New button from the Home tab, to start a new project

3. Initiate SSIS package documenting by clicking the Add button under the Integration services tab:

   

4. Select the appropriate package source from the Add integration services packages dialog, navigate to the package you want to document, and click the OK button:

   

5. When the package is included, it appears in the Package selection section:

   

6. The Package details section offers filtering package attributes that will be included in the documenting process:

   

7. When all of these are set, clicking the Generate button from the Home tab initiates the documenting process:

   

8. In the output document, navigate to the SSIS package node, and scroll down the page to see the Control flow diagram: