Documenting your SSIS package

Question

Documenting your SSIS package

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

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

Christian Cote SSC Veteran Points: 218 More actions · Answer 1

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

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 2

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

Brandon Carl Goodman SSCrazy Points: 2458 More actions · Answer 3

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.

Christian Cote SSC Veteran Points: 218 More actions · Answer 4

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

jos.haemers-599799 SSC Eights! Points: 929 More actions · Answer 5

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

villersk SSCrazy Points: 2007 More actions · Answer 6

Check out Microsoft SQL Server BI Metadata Samples and Toolkit.

F.L Right there with Babe Points: 734 More actions · Answer 7

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?

jos.haemers-599799 SSC Eights! Points: 929 More actions · Answer 8

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

P Jones SSChampion Points: 12361 More actions · Answer 9

P Jones

SSChampion

Points: 12361

October 13, 2009 at 1:33 am

#1065064

The link should have been

villersk SSCrazy Points: 2007 More actions · Answer 10

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.

keith_martin Old Hand Points: 367 More actions · Answer 11

The Microsoft one only handles SQL 2005 SSIS Packages.

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

http://sqlmetadata.codeplex.com

villersk SSCrazy Points: 2007 More actions · Answer 12

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

Harold Mendoza SSC Veteran Points: 296 More actions · Answer 13

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

villersk SSCrazy Points: 2007 More actions · Answer 14

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;

Christian Cote SSC Veteran Points: 218 More actions · Answer 15

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

Thanks,

Christian Cote