SSIS Package Documentor

SSIS Package Documenter

Creating an SSIS Package is easy and even lot of reference is available for the same, however when it comes to read or let me say document the content of the package it is a night mare. Especially, when you have many packages in case you are trying to study them or you want to review the coding standards.

I have to deal with a similar requirement and created a utility to read the package code. An SSIS package code is made up of XML made up from a specific XSD. We can read this code using SQL server xquery.

Let me take an example here. We can fetch all the task name details used in the package by querying the executables path till Object name.

value('declare namespace p1="www.microsoft.com/SqlServer/Dts";//DTS:Executable/DTS:Executables/@p1:ObjectName[1]', 'nvarchar(max)')

To query the xml code of the package, we have to load the complete package xml into a table, which can be done using cmdshell command.

First of all load all the package names with path in a temporary table.

SELECT @CommandLine = LEFT('dir "' + @Path + '" /A-D /B /S ', 8000);

               INSERT INTO @MyFiles (FullPath)

               EXECUTE xp_cmdshell @CommandLine;

 Load the package code in the table now one by one

DECLARE @sql NVARCHAR(max);

SET @sql = '

INSERT  INTO pkgStats (PackagePath,PackageXML)

               select  ''@FullPath'' as PackagePath

               ,cast(BulkColumn as XML) as PackageXML

               from    openrowset(bulk ''@FullPath'',single_blob) as pkgColumn';

SELECT @sql = REPLACE(@sql, '@FullPath', @FullPath)

EXEC sp_executesql @sql;

The main categories of the package code can be divided into four major parts:

The attached stored procedure script queries the complete package xml and generates an output report. The procedure accepts the input path for the package files and iterates over the files to document the package code. It uses a connection to a sql server database which creates temporary tables for the processing.

The output of the procedure returns the complete package details in the form of a grid report.