SSIS Package documentor

  • Comments posted to this topic are about the item SSIS Package documentor

    --Divya

  • Great article Divya!

    Cheers!

  • hr_sn (8/31/2015)


    Great article Divya!

    Cheers!

    Thanks for liking..hope it helps you.

    --Divya

  • What version of MSSQL is required to run this script? I tested id on MSSQL 2008, and it shows emtpy table....

  • Good work.

    I have only skimmed the article so far, but one question which came to mind relates to project-level parameters.

    As these are not part of the package DTSX, I am assuming that they present a bit of a problem?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil Parkin (9/1/2015)


    Good work.

    I have only skimmed the article so far, but one question which came to mind relates to project-level parameters.

    As these are not part of the package DTSX, I am assuming that they present a bit of a problem?

    The script works for ssis 2012. If you have ssis 2008, you can just migrate the dtsx file and run the script.

    --Divya

  • Phil Parkin (9/1/2015)


    Good work.

    I have only skimmed the article so far, but one question which came to mind relates to project-level parameters.

    As these are not part of the package DTSX, I am assuming that they present a bit of a problem?

    Project level parameters are not read through the script currently, only package parameters are there. I need to check I we can read project para from package, but I am sure we can read the values passed to project para.

    --Divya

  • This looks like a tool that I would like to use. Too bas My environment is 2008 R2 and SSIS 2008

    be nice to have a version that worked for me.

  • Looking forward to testing this tonight on one of my clients servers. 🙂



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • I like using WITH XMLNAMESPACES to avoid having "declare namespace DTS=" all over the place. And there are differences in the SSIS XML schema for 2005, 2008, and 2012, but they can be overcome with a little creativity.

    For example, this query lists the ConfiguationFilter value for all SQL configurations in all packages store in msdb. All my SQL configurations use a table name of dbo.SSIS_Config, so it's parsing that out. It handles the differences between 2005/2008 and 2012.

    WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS),

    folders AS (

    SELECT folderid, foldername = CAST('/' AS NVARCHAR(MAX))

    FROM msdb.dbo.sysssispackagefolders

    WHERE parentfolderid IS NULL

    UNION ALL

    SELECT c.folderid, foldername = p.foldername + c.foldername + '/'

    FROM msdb.dbo.sysssispackagefolders c

    INNER JOIN folders p ON p.folderid = c.parentfolderid

    WHERE c.foldername NOT IN ( 'Data Collector', 'Generated', 'Maintenance Plans' ) ),

    packages AS (

    SELECT PackageName = f.foldername + p.name, PackageData = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)

    FROM msdb.dbo.sysssispackages p

    INNER JOIN folders f ON f.folderid = p.folderid

    )

    SELECT pkg.PackageName, ConfigurationFilter = REPLACE(STUFF(pkg.Config, 1, CHARINDEX('"[dbo].[SSIS_Config]";', pkg.Config) + 22, ''), '";', '')

    FROM (

    SELECT packages.PackageName, Config = ISNULL(X2012.n.value('@DTS:ConfigurationString', 'varchar(1000)'), X2005.n.value('.', 'varchar(1000)'))

    FROM packages

    OUTER APPLY PackageData.nodes('/DTS:Executable/DTS:Configuration/DTS:Property[@DTS:Name="ConfigurationString"]') X2005(n)

    OUTER APPLY PackageData.nodes('/DTS:Executable/DTS:Configurations/DTS:Configuration') X2012(n)

    ) pkg

    WHERE Config LIKE '"%";"\[dbo\].\[SSIS_Config\]";"%";' ESCAPE '\';

    It doesn't see project configurations, and it doesn't find packages anywhere other than msdb.dbo.sysssispackages. I think if you want a tool to go through the solution file heirarchy parsing XML files, you probably would be better off using C# or another language rather than T-SQL.

  • Hmm... not too hard to do a small bit of googling (or better, searching on sqlservercentral.com) for how to ingest an XML file into a xml variable using TSQL instead.

    Once the SSIS .dtsx file is loaded into an xml variable, then it's just a relatively simple "excercise left for the reader" to incorporate the rest of the code in this article.

    Yes, I plan on using the code in my article to guide my efforts to do this with some sort of command-line tool (perhaps the XML add-on for gawk, or Powershell).

    I'm feeling like the next logical step in this then would be to work on some xsl or just straight-up programming to then convert the info grokked from a .dtsx package into a BIML file.

    Or, do a little bit of other work, and use graphviz, and the dot-to-Visio stuff, to make more useful Visio files diagramming the packages.

  • may i know the use of these command "EXECUTE xp_cmdshell"

  • Great work, thank you! One suggestion: it doesn't seem to play well in cases when there are complex multi-line expressions used for various properties. Maybe convert CR/LF into spaces, so that it would still work when copy/pasting into Excel.

    Actually I just tried it by making a small tweak to the output and it did the trick:

    ISNULL(REPLACE(REPLACE(ExpressionValue, CHAR(10), ' '), CHAR(13), ' ') ,'') +'"' ExpressionValue

    Could you explain how you manipulate the results to make them readable? Do you just place all the columns as "rows" in pivot table?

  • Misha_SQL (9/2/2015)


    Great work, thank you! One suggestion: it doesn't seem to play well in cases when there are complex multi-line expressions used for various properties. Maybe convert CR/LF into spaces, so that it would still work when copy/pasting into Excel.

    Actually I just tried it by making a small tweak to the output and it did the trick:

    ISNULL(REPLACE(REPLACE(ExpressionValue, CHAR(10), ' '), CHAR(13), ' ') ,'') +'"' ExpressionValue

    Could you explain how you manipulate the results to make them readable? Do you just place all the columns as "rows" in pivot table?

    Thanks. Yes for expression we can remove quotes from final select as in expression if we have some quotes then it breaks. You can lsend ne a sample package so that I can look into it

    --Divya

  • corey lawson (9/1/2015)


    Hmm... not too hard to do a small bit of googling (or better, searching on sqlservercentral.com) for how to ingest an XML file into a xml variable using TSQL instead.

    Once the SSIS .dtsx file is loaded into an xml variable, then it's just a relatively simple "excercise left for the reader" to incorporate the rest of the code in this article.

    Yes, I plan on using the code in my article to guide my efforts to do this with some sort of command-line tool (perhaps the XML add-on for gawk, or Powershell).

    I'm feeling like the next logical step in this then would be to work on some xsl or just straight-up programming to then convert the info grokked from a .dtsx package into a BIML file.

    Or, do a little bit of other work, and use graphviz, and the dot-to-Visio stuff, to make more useful Visio files diagramming the packages.

    --Divya

Viewing 15 posts - 1 through 15 (of 43 total)

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