i'm looking for a good method in order to document my ssis packages. i already tried this ssis documenter http://www.ssisdocumenter.com/ but was not very satisfied with it. what are your experiences with documenting ssis packages?

We too experienced this and decided to proceed with that manually....

This totally depends on what your documentation needs are. Are you just documenting the technical aspects of each of your packages, or are you also trying to capture business logic, justifications, functionality, etc? The former can be done a few different ways, while the latter should already be done ;-) For documenting the technical aspects of packages only, I have used C# to extract variables, log settings, connection information and the like. You can also bulk load packages into SQL Server (SELECT * FROM OPENROWSET(BULK N'somepackage.dtsx',SINGLE_BLOB) AS Package) and use XQUERY (http://msdn2.microsoft.com/en-us/library/ms345122.aspx)to get at everything you need (with some work). I once had a manager ask me to provide screenshots of all the workflows as "documentation" (he was a rather "visual" person without the ability to open packages on his own...). Basically, there is a large variety of what you can document.

Take a look at BI Documenter: http://www.bidocumenter.com It's not perfect, but I have not found a better tool out there, and it documentes more than just SSIS. Download it anc check it out. Of course, as Tod says, the tool you use really depends on what you need the documentation to do for what audience...

thanks ... i'm gonna try bi documenter. it was already clear to me that the type of documentation depends on the use of the package. i was just interested in the documentation methods of all you ssis pros in general

roakin wrote: thanks ... i'm gonna try bi documenter. it was already clear to me that the type of documentation depends on the use of the package. i was just interested in the documentation methods of all you ssis pros in general To be honest, most of the time I personally just use the packages themselves for documentation. Because SSIS packages are inherently visual, a well-annotated package can be shared with business stakeholders and deliver 80% of the value for 20% of the effort, which is usually a good thing. An ideal SSIS doc tool for me would need to: 1) Include the control flow and data flow logic graphically (this might be tricky due to the limitations of the output media) 2) Include developer-added annotation comments in the doc tool output (think "XML Comments" but for SSIS, perhaps where an annotation was begin with a special character sequence like ///logic or ///requirement or something and these sequences were picked up by the doc tool) 3) Include end-to-end data provenance and tracing, so that every field coming from any data source was traced through all transformations to fields in every destination (things like source queries based on expressions might make this tricky too) And I could probably think of much more, but these are thing things I find myself really wanting (and not finding) in existing SSIS doc tools. I don't think they'll come to pass any time soon, but I'll keep on hoping. And annotating my packages thoroughly, so that in the meantime they are their own documentation...

Good ideas. For item number 2, it seems that having the ability to "attach" annotations to control and data flow items -- so that they appear with those items in any outputted documentation -- would be a great thing. Personally, my ETL packages are documented in the following way:1.) Business and functional requirements (3 or 4 pages of numbered requirements by business owners, stewards, and others). This document typically states the needs of the package as a whole. When a new developer, DBA, tester, et al comes on board... they read this first. Additionally, it is used for user-acceptance testing (if you do that sort of thing in the back room -- sometimes we do, sometimes we don't)2.) A logical data map that lists all the source-to-target mappings complete with all expected transformations, lookups, and SQL statements (pseudo-code is OK). This can be a beast to set up and requires a ton of discipline to see it through to the end. However, the LDM is so important for data quality, lineage, compliance, development, administration, maintenance, etc, that all the hard work is well worth the effort. Although I don't use an LDM relational database as part of the Integration engine in SSIS, I HAVE used the logical map as a physical table-driven ETL engine using Visual FoxPro with great success (hand-coded ETL) in the near past.3.) The last document I need to have is a document that lists and defines all the package settings. These would include: configuration data; variable names, domains, and ranges; connections; log settings; event handlers; and parent package/child package relationships. For the rest, if the above three documents don't give me what I need, I just open the package. This goes back to my days as a programmer -- you would never document every line of code, nor would you document each branch, procedure, or method inside the blackbox. You document only what is most useful for newbies, management, and context -- meaning that when I open a package, I don't want to have to scratch my head for 20 minutes wondering what it is supposed to be doing! Nor would I want to read through 80 pages of technical documents only to discover that the function I am looking at has since changed and the document was not updated...

When you document SSIS package you should consider the following items. - Documenting Visual Work Flow for Control Flow - Documenting Visual Work Flow of DataFlow - Documenting Mappings - Documenting Variables, Tasks, - Documenting Source/Targets etc Have you looked at new version of BIDocumenter ... It has really nice features for SSIS Documentation need SSIS-Tutorials-FAQs | Convert DTS to SSIS | Document SSIS | SSIS Tasks | Real-time SSIS Monitoring