SQL Server Integration Services ( SSIS ) Package Documentation | How to write SSIS Package Documentation

After creating SQL Server Integration Services Package, the next step is to have the document ready for that package. Here are some important points those I keep in mind while writing document for my SSIS Package.

PURPOSE

Explain the purpose of SSIS Package in few lines.

JOB FREQUENCY DETAILS 

Explain how the Package is going to be executed, SQL Server Agent job is going to call or any other third party scheduler is going to call the package and often (Schedule) the package will execute.

EMAIL NOTIFICATIONS DETAILS

 Explain what type of notification package creates, In case of data validation which team will be notified, or in case of hard failure (Connection failure etc.) which team the package will send an email. Explain with snapshots how the emails will look like so Production Support and other team member would know how the email will look like.

AUDIT PROCESS ON PACKAGE START

If your package is using any audit information of start of package, It is good practice to have some audit tables where your package should put some information at start such as package name, start time etc. So you can track which packages are currently running and also see execution history of different packages.

EXTRACTION PROCESS 

Explain about extraction process, this heading can be further divided into sub heading

• Source 

Explain from which Server, Folder or FTP Site you are extracting data. Provide the Server Name, Folder Path or Ftp Site Address and other detail information about Table/s or file/s which you are using as source.

• Transformations And Business Logic

Explain about the business logic and transformations you are using, it can be just a snapshot with some text about logic those you have implemented by using those transformations.

• Destination

Explain the destination you are using, if special settings are used provide them as well.

AUDIT INFORMATION ON END OF PACKAGE

Each package should put some information in audit table once completed. This information can be used to see if package completed successfully. If you do not have created custom Auditing then you can ignore this part of documentation.

DEPENDENCIES

Provide list of all the objects (Stored procedures, tables, views, function, child package etc.) on which the package depends for successful execution.

TROUBLE SHOOTING

Provide the list of the common error/failure those can happen and in case of those errors/failure how a developer can start looking into package and troubleshoot. 

DYNAMIC PACKAGE CONFIGURATIONS

Provide which type of SSIS Configuration is being used in the package; provide details about connection managers, variables and any other information that is maintained by using SSIS configuration.  In case of SQL Server Configuration provide the Database name and table and in case of XML configuration provide the file path.

LOGGING AND EVENT HANDLING

Explain which type of logging is used in package. What type of event’s information is captured and what actions are done in Event handler pane. Provide the information about the path (Text file logging) or database name where log table exists.

IMPORTANT LINKS

If Business required document ( BRD) , Functional required document( FRD) or any other documentation was provided for package in shared folder or SharePoint site , please those all important link in this section.