Post reply

Documenting your SSIS package

  • July 31, 2008 at 3:52 am

    #851117

    HI, I personally use Visio diagrams to document my package. I use visio toolbox templates for SQL Serer (http://visiotoolbox.com/downloads.aspx?resourceid=1&aid=558) to create my diagrams and use them in a word document. There are also other commerciaql products that can help you on this like ssis documenter (http://www.ssisdocumenter.com).

    hth,

    Ccote

  • July 31, 2008 at 4:17 am

    #851129

    Christian Cote (7/31/2008)

    HI, I personally use Visio diagrams to document my package. I use visio toolbox templates for SQL Serer (http://visiotoolbox.com/downloads.aspx?resourceid=1&aid=558) to create my diagrams and use them in a word document. There are also other commerciaql products that can help you on this like ssis documenter (http://www.ssisdocumenter.com).

    hth,

    Ccote

    Nice tip on the visiotoolbox.... where do you installed the files to, though (the VST etc.) - there doesn't seem to be an install guide

  • July 31, 2008 at 7:00 am

    #851225

    If you wish to go the software route, we recently purchased SSIS Document (http://www.ssisdocumenter.com/) that we have found to be a very good product. Also has the capability of integrating into the BIDS IDE, making documentation very easy.

  • July 31, 2008 at 11:16 am

    #851432

    HI, to add the templates and stencils to be available in visio, you can find the procedure here http://groups.google.com/group/microsoft.public.visio.general/browse_thread/thread/2f108ba23ec076d5/75adec6860f5e518?lnk=st&q=#75adec6860f5e518

    Ccote

  • August 5, 2008 at 1:05 pm

    #853437

    Harold,

    Have you ever heard of BI Documenter? http://www.bidocumenter.com/

    That's a tool you can use to document your SSIS Packages.

    Kindest Regards,

    Jos Haemers

  • June 25, 2009 at 8:25 am

    #1015338

    Check out Microsoft SQL Server BI Metadata Samples and Toolkit.

  • September 22, 2009 at 6:14 am

    #1056698

    Anyone has some more suggestions on how to do this?

    Is there anything built in for MS SQL 2008?

    Also, The visio url seems not to work anymore. Anyone knows the name for the templates?

  • October 12, 2009 at 12:51 pm

    #1064899

    Try using BI Documenter. It's not for free, but it can handle the things you want.

  • October 13, 2009 at 1:33 am

    #1065064

    The link should have been

  • October 13, 2009 at 5:06 am

    #1065126

    FYI - The Microsoft BI Toolkit is a C#.NET project that takes SSIS projects and generates visio like diagrams.

    I studied that and a project called 'Metashare' to get a grasp on extracting information from SSIS. In doing so I became pretty proficient at generating SSIS packages programatically.

    You can either parse the xml or use the ssis .net api.

    All the documenting applications out right now do a horrible job with data lineage. In my opinion that is a pretty important part of documenting what is primarily an 'ETL' tool.

    I chose to go with a custom solution and found it pretty worthwhile.

    Also, ssisdocumenter.com used to let you generate documentation online for free by uploading packages. Not sure if they still do or not.

  • November 29, 2009 at 4:51 am

    #1085127

    The Microsoft one only handles SQL 2005 SSIS Packages.

    There is now an SQL 2005/2008 version available on Codeplex.

    http://sqlmetadata.codeplex.com

  • November 30, 2009 at 4:50 am

    #1085342

    The prvious version could not find objects inside of a sequence container. Is this still true?

  • May 18, 2010 at 6:45 pm

    #1169181

    Adding to my original question. The main idea was to be able to include documentation notes as you add SSIS objects. I have a numeration system for each object so they are organized sequentially. I was thinking to generate SSIS documentation by extracting the name of the object and their respective notes to a text file or url, so that documents and notes could be created on the fly.

    I also wanted to add to this a place holder at the beginning so to hold summary level information.

    Any ideas around?

    Thanks

  • May 19, 2010 at 5:34 am

    #1169335

    Have you invesitigated third party software solutions or are you planning on writing custom code?

    If you are planning on creating a .net solution you will need to

    -load the package

    -loop through the control flow executables collection

    -if the control flow executable is not a data flow or container than write out the name and description

    -if it is a datflow or container you will need to initialize and loop through it's executables collection and write out its properties.

    I created a small app for personal use that searches for a given column name in all datafows of all packages in a solution and writes out a detailed .html report. It reports all objects involved in the columns lineage of the dataflow in order of the data flow and includes name, description and a bunch of other things I cant remember right now. I havent used it in a while.

    Will gladly give you the source.

    Here is a snippet that would get you started:

    //define our package and aplication

    Package pkg;

    pkg = new Package();

    Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();

    //load package

    pkg = a.LoadPackage(@GlobalClass.global_package, null);

    //get control flow items as an executable collection

    Executables kvexecs = pkg.Executables;

  • May 19, 2010 at 8:56 am

    #1169490

    HI, I would be very interested in having the source code. Sounds that it would help me a lot.

    Thanks,

    Christian Cote

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply