Search results matching tag 'XPath'

Editing sqlcmdvariable nodes in SSDT Publish Profile files using msbuild

jamiet — Mon, 10 Dec 2012 13:42:51 GMT

Publish profile files are a new feature of SSDT database projects that enable you to package up all environment-specific properties into a single file for use at publish time; I have written about them before at Publish Profile Files in SQL Server Data Tools (SSDT) and if it wasn’t obvious from that blog post, I’m a big fan!

As I have used Publish Profile files more and more I have realised that there may be times when you need to edit those Publish profile files during your build process, you may think of such an operation as a kind of pre-processor step. In my case I have a sqlcmd variable called DeployTag, it holds a value representing the current build number that later gets inserted into a table using a Post-Deployment script (that’s a technique that I wrote about in Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experiences – search for “Putting a build number into the DB”).

Here are the contents of my Publish Profile file (simplified for demo purposes) :

Notice that DeployTag defaults to “UNKNOWN”.

On my current project we are using msbuild scripts to control what gets built and what I want to do is take the build number from our build engine and edit the Publish profile files accordingly. Here is the pertinent portion of the the msbuild script I came up with to do that:

  <ItemGroup>
    <Namespaces Include="myns">
      <Prefix>myns</Prefix>
      <Uri>http://schemas.microsoft.com/developer/msbuild/2003</Uri>
    </Namespaces>
  </ItemGroup>
 
  <Target Name="UpdateBuildNumber">
    <ItemGroup>
      <SSDTPublishFiles Include="$(DESTINATION)\**\$(CONFIGURATION)\**\*.publish.xml" />
    </ItemGroup>
    <MSBuild.ExtensionPack.Xml.XmlFile Condition="%(SSDTPublishFiles.Identity) != ''"
                                       TaskAction="UpdateElement"
                                       File="%(SSDTPublishFiles.Identity)"
                                       Namespaces="@(Namespaces)" 
                                       XPath="//myns:SqlCmdVariable[@Include='DeployTag']/myns:Value" 
                                       InnerText="$(BuildNumber)"/>
  </Target>

The important bits here are the definition of the namespace http://schemas.microsoft.com/developer/msbuild/2003:

and the XPath expression //myns:SqlCmdVariable[@Include='DeployTag']/myns:Value:

Some extra info:

I use a fantastic tool called XMLPad to discover/test XPath expressions, read more at XMLPad – a new tool in my developer utility belt
MSBuild.ExtensionPack.Xml.XmlFile is a msbuild task used to edit XML files and is available from Mike Fourie’s MSBuild Extension Pack
I’m using a property called $(BuildNumber) to hold the value to substitute into the file and also $(DESTINATION)\**\$(CONFIGURATION)\**\*.publish.xml to define an ItemGroup all of my Publish Profile files. Populating those properties is basic msbuild stuff and is therefore outside the scope of this blog post however if you want to learn more check out MSBuild properties & How To: Use Wildcards to Build All Files in a Directory.

Hope this is useful!

@Jamiet

XMLPad – a new tool in my developer utility belt

jamiet — Fri, 11 Feb 2011 19:22:00 GMT

Yesterday I was on the lookout for a free tool that would help me write XPath statements. I put a shout out on Twitter and Johan Barnard replied saying :

Give XMLPad a try http://www.wmhelp.com/xmlpad3.htm

I’m sure there are legions of developers out there that know all about XMLPad but I had never heard about it so I suspect some of you reading haven’t either. Today I downloaded it to give it a run out and I gotta say – I love it. I only used it to do one thing –constructing an XPath expression to point to a particular Configuration definition in a .dtsx file- and it allowed me to do that with consummate ease. The feature I particularly loved was that, similar to Google Suggest, it showed me results from my expression as I typed.

Here is a screenshot of my XPath expression to find (and just try saying this in a hurry)

the value of a property whose DTS:Name attribute equals ‘ConfigurationString’ of a Configuration definition where the value of that Configuration definition’s property whose DTS:Name attribute equals ‘ObjectName’, equals ‘BIConfig

My XPath expression:

/DTS:Executable/DTS:Configuration[DTS:Property[@DTS:Name=’ObjectName’]=’BIConfig’]/DTS:Property[@DTS:Name=’ConfigurationString’]

and believe me, there was no way I would have been able to come up with that without a tool to help me!

So, an easy tip for you – if you need to write XPath expression download XMLPad for free from http://www.wmhelp.com/xmlpad3.htm and see what it can do for you.

That’s all. Its now Friday evening and I’m shutting down and relaxing before heading to the big game at Twickenham tomorrow (yes, I have a ticket ). Have a good one!

@Jamiet

SSIS Package Stats Collector version 2

jamiet — Sat, 07 Nov 2009 01:58:24 GMT

A few weeks ago I published a blog entitled Collecting information about your SSIS packages which demonstrated a way that you could use T-SQL and XQuery to derive information about your SSIS packages. Since then I’ve made a few amendments to the script that accompanied that blog post as follows:

New columns

The collected data now includes the following information about your packages:
    PackageId
    PackageXML (The XML that forms the .dtsx file)
    NumberOfContainers
    NumberOfDataflows
    NumberOfConnectionManagers
    NumberOfVariablesInEntirePackage

New feature

Returns a list of non-unique GUIDs and the packages that they appear in. If a GUID appears more than once in the same package it tells us how many times

Bug fixes

If there were no tasks in the package no data would be returned. That is now no longer the case.
Previous version didn’t work on case-sensitive collations, should be OK now.

There are also more comments liberally sprinkled around it now as well!

Here’s a screenshot showing some of those new columns:

Returning [PackageId] as a field is an important addition because we can now very easily see if any of our packages share the same Package Id. Package IDs are supposed to be unique but if they are not then they are a problem waiting to happen and unfortunately Business Intelligence Design Studio (BIDS) doesn’t really protect you against it very well, in fact if you use package templates then its very likely that you have non unique GUIDs somewhere. (“Non unique globally unique identifiers” – that should set the alarm bells ringing if nothing else).

As alluded to above the script now returns a brand new dataset showing all the GUIDs that occur more than once across all of your packages. Now, I’m not just talking about PackageId GUIDs here, every single object in a SSIS package be it a task, container, component, variable, connection manager, column, whatever… has a GUID attached to it and given the healthy amount of copy-and-paste that goes on when developing SSIS packages its a sure bet that some of those GUIDs are going to get repeated somewhere. Of course, its up to you whether you want to do anything about it or not – if you’re a purist then the idea of repeated GUIDs might keep you awake at night although I suspect most people simply won’t care. Still, its there if you need it:

You may wonder why I have the [NumberOfTimeshisGUIDOccursInThisPackage] column in there. Well, its perfectly plausible that the a GUID can reoccur in the same package and in fact when I was testing this earlier on today I had a package that had 6 occurrences of a GUID because it contained 6 near-identical connection managers that had obviously been copied-and-pasted.

That’s it for now. Let me know if this script proves useful to you and let me know if there’s anything you’d like to see added to it. Download the latest version from: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20091105/SSISPackageStatsCollector%5E_v2.sql. I have also dropped it into SQLMesh.

Looking forward to some feedback (hint hint).

@Jamiet

Collecting information about your SSIS packages [SSIS Nugget]

jamiet — Sun, 18 Oct 2009 21:08:00 GMT

Did you know that is is possible to read the contents of a SSIS package (i.e. a .dtsx file) from within SQL Server Management Studio (SSMS) using T-SQL? For example, take the following T-SQL snippet:

select    cast(BulkColumn as XML)
from    openrowset(bulk 'C:\tmp\MyPkg.dtsx',
                        single_blob) as pkgColumn;

It uses OPENROWSET to return the contents of a specified package (C:\tmp\MyPkg.dtsx) as an XML document. Here is a screenshot showing what this returns:

and clicking on that result opens up the following:

That’s what the inards of a .dtsx file look like. In other words we now have a queryable XML document representing a package, thereafter you’re limited only to what you can do with XQuery which is quite a lot. Now, I’m no XQuery expert by any means but I did manage to find a few uses for this. Here are some sample queries:

All properties of a package

SELECT    Props.Prop.query('.')                                                        as PropXml
,        Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            string(./@p1:Name)','nvarchar(max)')                    as PropName
,        Props.Prop.value('.', 'nvarchar(max)')                                        as PropValue
FROM    (
        SELECT    CAST(pkgblob.BulkColumn AS XML) pkgXML
        FROM    OPENROWSET(bulk 'C:\tmp\MyPkg.dtsx',single_blob) AS pkgblob
        ) t
CROSS    APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
                        /DTS:Executable/DTS:Property') Props(Prop)

I had a lot of help from @rbarryyoung with this one!

Name and type of every task in a package

SELECT    Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            ./p1:Property[@p1:Name=''ObjectName''][1]','nvarchar(max)') as TaskName
,        Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            ./@p1:ExecutableType','nvarchar(max)') as TaskType
FROM    (
        select    cast(pkgblob.BulkColumn as XML) pkgXML
        from    openrowset(bulk 'C:\tmp\Package.dtsx',single_blob) as pkgblob
        ) t
CROSS    APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
                            //DTS:Executable[@DTS:ExecutableType!=''STOCK:SEQUENCE''
                        and    @DTS:ExecutableType!=''STOCK:FORLOOP''
                        and    @DTS:ExecutableType!=''STOCK:FOREACHLOOP''
                        and not(contains(@DTS:ExecutableType,''.Package.''))]') Pkg(props)

Note that this one will also return all tasks that exist in eventhandlers and it ignores how “deep” a task is in the container hierarchy.

Putting it all together

Ok, that’s all pretty cool but it would be nice to combine it all together and get a summary of many packages, perhaps all of the packages on your machine. Hence I’ve put together a T-SQL script that will display summary information about all of the packages in a folder and its subfolders. Here’s the results of running that script on my dev machine:

217 packages – I’ve collected lot of them over the years! This shows a sampling of some of the information that it is possible to collect:

Package name
Original creator of the package
Package Type (signifies which version of BIDS was used to originally build it)
Version numbers
Number of tasks in the package

Want to know what your most complex package might be? Simply order this dataset in descending order of [NumberOfTasks]. Or maybe you want to know which of your developers has built the most packages – the answers are right here!

Of course, this could be extended to capture much much more information than what I have captured here. You may want to know how many eventhandlers each of your packages has, when the packages were created, or perhaps how many components are in your dataflows. The (SSIS) world is your oyster!!!

One thing that I thought would be very useful would be to stick this script into a SQL Agent job, run it on a daily basis, and insert the results into a history table thereby giving you a running history of all the packages in your system and when they changed. If anyone does do that let me know how it goes!

To execute the script simply open it and change the following line as appropriate:

DECLARE    @Path    VARCHAR(2000) = 'C:\*.dtsx';

Note that you will need to enable [xp_cmdshell] in order to run the script which is available on my SkyDrive at:

http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20091018/SSISPackageStatsCollector.sql

If you experience any problems with it let me know. If you adapt it any way let me know that too because writing these XQuery statements is no easy task (believe me!!!) and it would be great to share that stuff with other people!

@JamieT

Update: The original version of this script only worked on SQL Server 2008. I have now updated it so that it works on SQL2005 also! Thanks to Bruce in the comments for alerting me to this fact/

Update 2: Found a few more issues and hence have uploaded another new version (same link still works though). Changes:

Script will now work on a server with a case-sensitive collation
xp_cmdshell is turned on at the top of the script
An error message that could get returned by the command-line call under certain circumstances wasn't getting handled. It is now!

Update 3: An updated version with bug fixes and new features is now available at SSIS Package Stats Collector version 2

XML Optimization Presentation Sample Code

Mike C — Sat, 16 May 2009 11:13:00 GMT

During the XML Optimization presentation today I'll refer back to the sample code posted at http://www.apress.com/book/downloadfile/4021. I'll also update this post with additional sample code by tomorrow.

UPDATE: The PowerPoint presentation and sample code are attached to this post in the ZIP file.

Jacob Sebastian's book "The Art of XSD" is available here: http://www.red-gate.com/specials/Ebooks/XSD_0409.htm.

Thanks to everyone who turned out for the NJSQL Performance Tuning Saturday event today, and thanks to Melissa for coordinating it!

NJSQL website: http://njsql.org/Default.aspx