January 29, 2009 at 8:12 am
When creating a package in SSIS what is the best demonstrated practice (BDP) for documenting a package? I need enough documentation so that a new user to the system can understand the package, without going hog-wild and creating a beast of a document to support the package.
The typical package that we see in our shop may have Execute SQL Tasks, Data Flow Task, or FTP Tasks; basically simple ETL functions.
I considered adding an Execute SQL Task (not enabled) and using it as a sort of ReadMe file... Is that a bad idea? It seems to me that the text annotation just simply doesn't allow for enough documentation for my needs.
I am a business analyst and a relatively new user to SSIS and do not have a "programming background". So please excuse my newbie stature by posing such a simple question.
January 29, 2009 at 11:35 am
If you refer to microsoft project real's best practices that will help you. In general it it up to you to add comments and labelling to your control flows and data flows. Adequately document event handlers that could be missed.
Project Real is open source. Open a few of its packages to see the documentation.
http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx
January 29, 2009 at 6:42 pm
I would not suggest using disabled tasks to store comments. You can use multiple annotations in the control flow and data flow. You can put one next to each task if you want.
I would suggest:
A header annotation that gives a brief (few sentence) description of what the package does.
Make sure each task has a meaningful name that briefly describes what it does. An Execute SQL task with a name like “Truncate Staging Tables” probably doesn’t need too much more documentation.
If the tasks lend themselves to being placed in groups use sequence containers and give the containers descriptive names. This helps to give a high level overview of what the package is doing.
If your packages have Execute SQL tasks I would put the SQL in stored procedures if at all possible. Stored procs are usually more conductive to adding inline comments and can give some separation between the package and the database.
If you are creating the SQL dynamically and then executing the string have an annotation next to the Execute SQL task that has an example of what the final SQL statement should look like. This way you don’t have to pick through the package and try and build the statement yourself. The annotation might also contain comments as to what variables, expressions, script tasks etc contribute to the SQL statement.
August 24, 2009 at 2:58 pm
I agree that the new SSIS is lacking a good documentation tool. I can always tell what a package is doing; but I can't always tell why. Nor can I see who the business owner is, or who originally wrote the package, or who the contact is from the other company, etc.
In the real world I'll need to revisit the feed in 6 months and have no idea what's going on. "What the heck was I thinking when I wrote this?"
I'm looking into Source Safe as a possible option for additional comments for SSIS packages.
A nod is as good as a wink to a blind bat.
August 25, 2009 at 7:59 am
I ahve to admit all of my documentation is done in a word doc of the same name as the package. This can be placed in with the packages or stored elsewhere. The document has a standard layout. There are sections for the version control, summary, challenges posed by the package, control flow, data flow, event handling, package logging, error handling, package variables and an appendix for sql elements such as view and table definitions. The control and data flows document the summary of what is occuring and each flow is broken down and each component part is documented. Additionally I also include items in the appendix such as Execute SQL tasks that have result sets or parameters. Other items recorded are any expressions used and generally anything that is not obvious when you first look at the package.
The documentation is generally developed on the assumption that I will not be there and the person diagnosing any problems will not be a SSIS developer.
Ells.
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply