How to pull XML out of SSIS so I can study it with XPath

Narveson has asked for the wisdom of the Perl Monks concerning the following question:

We have hundreds of SSIS packages (big database manipulation programs that Microsoft intends you to view and edit through a GUI). Having to open hundreds of things in a GUI to do some chore (never mind what) is always tedious. So I was delighted to learn that the packages are saved as XML and the XML can be retrieved by the following SQL query:

SELECT  CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata)) AS XMLText
FROM msdb.dbo.sysssispackages
[download]

This query works when I submit it in Microsoft SQL Server Management Studio.

But imagine my sorrow when I tried the same query in DBI and got

DBD::ODBC::st fetch failed: [Microsoft][SQL Server Native Client 10.0]
+String data, right truncation (SQL-01004)
[download]

This is with LongReadLen set to 2147483647, the largest allowed.

So XML parsing of these objects is still tantalizingly out of reach of my Perl programs.

Must I confine myself to Microsoft's own proprietary tools?

Comment on How to pull XML out of SSIS so I can study it with XPath Select or Download Code

Replies are listed 'Best First'.
Re: How to pull XML out of SSIS so I can study it with XPath by Corion (Patriarch) on Aug 27, 2010 at 20:27 UTC
This post suggests that `$dbh->{LongTruncOk} = 1;` [download] "works". Likely, just in the sense of truncating your result. Maybe that XML-SQL has a way to fetch only parts of it?	[reply] [d/l]
Re: How to pull XML out of SSIS so I can study it with XPath by dasgar (Priest) on Aug 27, 2010 at 20:28 UTC
I haven't tried using Perl to connect to databases yet, so I'm not sure I could provide help with the DBI aspect. However, here's a work around that could get you going in the mean time. Save your SQL statement in a text file (for example, query.sql) Use sqlcmd (or osql for older SQL Server versions) to run the SQL statement and put the output in a file. (For example, `sqlcmd -U user -P pwd -d database -i query.sql -o query.out`) Use the output file as the source file for your Perl script Kind of convoluted, but it should get you there. If you really wanted to, you could get Perl to create the '.sql' file and then use something like a piped open to run the sqlcmd command and read in its output. By the way, you'll need the SQL client tools installed. Of course if you have the management studio installed, I would imagine that you have the client tools installed too.	[reply] [d/l]
Re: How to pull XML out of SSIS so I can study it with XPath by sundialsvc4 (Abbot) on Aug 30, 2010 at 15:49 UTC
I am not well-versed in this area (I generally try to avoid Microsoft as much as possible ... `;-)` ...), but what pops into my mind is: “maybe this is a BLOB-type field.” As you well know, “BLOB = Binary Large OBject” is a field-type that can hold an arbitrary amount of data, and what you can do is to, by some means, open a handle to that field-value which then allows you to do file-like things to that value no matter how large or small it is ... repositioning, reading and writing chunks of data, and so on. So, it just seems to me that you could bind such a Perl object to the field (`XMLText`) and read it that way. (He said, waving his hands wildly in the air and trying to look very impressive ...)


Pathologically Eclectic Rubbish Lister
	PerlMonks