August 31, 2015 at 9:08 pm
Comments posted to this topic are about the item SSIS Package documentor
--Divya
August 31, 2015 at 9:09 pm
Great article Divya!
Cheers!
August 31, 2015 at 11:35 pm
hr_sn (8/31/2015)
Great article Divya!Cheers!
Thanks for liking..hope it helps you.
--Divya
September 1, 2015 at 3:33 am
What version of MSSQL is required to run this script? I tested id on MSSQL 2008, and it shows emtpy table....
September 1, 2015 at 3:42 am
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
September 1, 2015 at 5:15 am
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
September 1, 2015 at 5:19 am
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
September 1, 2015 at 7:47 am
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.
September 1, 2015 at 8:01 am
Looking forward to testing this tonight on one of my clients servers. 🙂
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]
September 1, 2015 at 8:06 am
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.
September 1, 2015 at 10:55 am
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.
September 2, 2015 at 1:16 am
may i know the use of these command "EXECUTE xp_cmdshell"
September 2, 2015 at 5:16 pm
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?
September 3, 2015 at 3:58 am
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), ' ') ,'') +'"' ExpressionValueCould 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
September 3, 2015 at 3:59 am
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