Document your SSIS Packages with BI Documenter Part1: Creating the Document.

November 28, 2011

Have you ever inherited an ETL environment and tied to make heads or tails of what was going on?  The previous developer didn’t leave any documentation. You have deadlines to meet and you need to know which packages do what. Does this sound familiar?  The worst is when you do this to yourself, a few years ago you created a package at someone’s request and now they’ve come back to you asking about that package and you can’t remember anything about it.  Proper technical documentation is so important in today’s world.  It can mean a few hours updating some packages for a downstream application when done right, or several days of searching and deciphering or maybe starting at square one in the worst case when documentation is lacking.  I have come across these problems working with clients on occasion and having access to a documentation tool has been a life saver.

BI Documenter is a great tool for documenting your SQL Server environment.  This does more than just document SQL Server Databases and Server Instance, though it does this very well for SQLServer 2000, 2005, 2008 and R2.  It will also document SSIS Packages (SQL Server Integration Services 2005, 2008), document SSRS Reports (SQL Server Reporting Services 2005, 2008), as well as document SSAS Cubes and Databases (SQL Server Analysis Services 2005, 2008).  Since I have been doing a lot of SSIS work lately I thought I’d show you how BI Documenter can help you document existing SSIS Packages.

BI Documenter has a very streamlined and easy to use interface. Launch BI Documenter from the start menu. When it has launched click on add new solution.  A solution here is very similar to a solution inside the BIDS environment.  The solution can contain documentation for any and all parts of the SQL Server stack you want to document.


Give the solution a name, and a description if you like and Save.


As you can see in the screenshot below I now have two solutions to choose from.  To select the solution you want to work with you can either double click on the solution or make sure the solution you want to select is highlighted and then click next.


On the next screen you can add what object types you want to document.   For this we will just choose an SSIS package, though we could choose multiple packages, a SQL Server Database and SSRS report all in one solution.


Clicking on Add Integration Services Package will bring up a new window to specify the location of the package you want to add.


Here you can specify the storage of the package, there are six choices, three each for SQL Server 2005 and 2008.  For each one you can specify File System, SQL Server or Package Store.  You can then set the authentication user name and password if you need to, and then specify the package path. Since we are using the file system in this example we will specify the containing folder and click next.


While there is only one package in this folder at the moment, BI Documenter does allow you to multi select packages here. Also please note that you can add descriptions as well as supply the package password if you have set the appropriate protection level on the package.  Once you have the packages that you wish to document selected click finish and this will bring you back to the solution page.

As you can see now there is now a package underneath the Integration Services Packages folder.


If you want to add any additional objects to the solution you could do that here, or if you’re done click Next.    On the next screen we will create the snapshot.  There are three tabs on this screen.  The first is the General tab. Here you can name the snapshot and give it a description.


The next tab it the filter tab.  Here you can select which object inside the solution you want to document.


As you can see you can choose to document individual components inside the  package, not just the package itself.  The third tab has more advanced configuration options that will be left at the default settings for now.  Lets click next and take the snapshot.  From this snapshot we will be able to create our documentation.


Choose the name of you snapshot from the dropdown, and if there are multiple objects in the snapshot you can select them as well.  When your snapshot is selected click next.  On the next screen you will finalize your documentation.  There are two choices for the document type.  It can be either a Microsoft Help File (.CHM) or an HTML file.  Choose the .CHM file here and specify a file location for the documentation.


The tabs across the top allow for further customization to our documentation.  The Template tab allows you to customize things like font color and size as well as add a custom logo to the document if you’d like.  The Diagram tab will allow you to create work flow diagrams to the documentation.  The Files tab allows you to add other documents and images to the document.  Of the next four tabs we only care about the Integration Objects.


When you’ve selected the options for the document that you want click next and once the document has been created click finish.  Your newly created documentation for the SSIS Package should now be open in front of you. 

To cover the extent of documentation that BI Documenter produces would make this blog post unbearably long, and I’m pretty sure that if I don’t want to write that much in one sitting, you probably won’t want to read that much.  Over the next few days I’ll post additional parts to this series where we can take a look at each section of the documentation that you have just created.  In the mean time please download BI Documenter and give it a try yourself.


One comment

  1. […] while back I wrote a series of blogs on using BI Documenter, and since then there have been some updates to BI Documenter which I believe are worth mentioning. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: