Search code examples
xsltssisexslt

Using/implementing exsl function node-set() in SSIS's XML Task

I'm attempting to apply an XSL transform to an XML file inside an SSIS package XML Task.

All well and good, but unfortunately my XSL is slightly less "portable" than normal as I need to use the function node-set(). A simplified example of my XSL is:

<xsl:for-each select="msxsl:node-set($familyNames)/token">
  <xsl:call-template name="PersonNameComponent">
    <xsl:with-param name="nameComponentType" select="'S'" />
    <xsl:with-param name="nameComponentSeqNo" select="number($noOfGivenNames) + position()" />
    <xsl:with-param name="nameComponent" select="." />
    <xsl:with-param name="nameTypeName" select="$familyName" />
    <xsl:with-param name="roleCode" select="$roleCode" />
  </xsl:call-template>
</xsl:for-each>

I'm using the following namespace in the stylesheet declaration:

xmlns:msxsl="urn:schemas-microsoft-com:xslt"

This works in the VS IDE, XMLSpy (as long as I set the XSLT engine as MSXML), etc. However, when I attempt to execute an XML Task within the package I get the following exception:

Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Function 'msxsl:node-set()' has failed.".

I'm using VS2005 to design the package as it's the 2005 version of SSIS.

Any ideas on how I can proceed are greatly appreciated.

I'm calling a template that implements the EXSLT str:split function to"tokenise" a string into it's constituent elements, e.g. "Kermit T Frog" would be returned as follows:

<token>Kermit</token>
<token>T</token>
<token>Frog</token>

This is stored in the variable $familyNames, which I then iterate through. However, as this is returned as a result tree fragment I need to wrap it with the function msxsl:node-set() so that the result is treated as a node set. Not sure how else I can achieve the above.

Here's the implementation of str:split that I obtained from http://www.exslt.org/:

<xsl:template name="str:split">
    <xsl:param name="string" select="''" />
  <xsl:param name="pattern" select="' '" />
  <xsl:choose>
    <xsl:when test="not($string)" />
    <xsl:when test="not($pattern)">
      <xsl:call-template name="str:_split-characters">
        <xsl:with-param name="string" select="$string" />
      </xsl:call-template>
    </xsl:when>
    <xsl:otherwise>
      <xsl:call-template name="str:_split-pattern">
        <xsl:with-param name="string" select="$string" />
        <xsl:with-param name="pattern" select="$pattern" />
      </xsl:call-template>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template> 
<xsl:template name="str:_split-characters">
  <xsl:param name="string" />
  <xsl:if test="$string">
    <token><xsl:value-of select="substring($string, 1, 1)" /></token>
    <xsl:call-template name="str:_split-characters">
      <xsl:with-param name="string" select="substring($string, 2)" />
    </xsl:call-template>
  </xsl:if>
</xsl:template> 
<xsl:template name="str:_split-pattern">
  <xsl:param name="string" />
  <xsl:param name="pattern" />
  <xsl:choose>
    <xsl:when test="contains($string, $pattern)">
      <xsl:if test="not(starts-with($string, $pattern))">
        <token><xsl:value-of select="substring-before($string, $pattern)" /></token>
      </xsl:if>
      <xsl:call-template name="str:_split-pattern">
        <xsl:with-param name="string" select="substring-after($string, $pattern)" />
        <xsl:with-param name="pattern" select="$pattern" />
      </xsl:call-template>
    </xsl:when>
    <xsl:otherwise>
      <token><xsl:value-of select="$string" /></token>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>
Solution

  • I've come up with a work-around that involves using a custom Script Task instead of the XML Task to transform the XML. The code in the script task is:

    Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports Mvp.Xml.Common.Xsl

Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()

        Dts.TaskResult = Dts.Results.Failure

        If Dts.Variables.Contains("FullSourcePathFileName") AndAlso _
            Dts.Variables.Contains("XsltPath") AndAlso _
            Dts.Variables.Contains("FullSourceTransformedPathFileName") Then

            Dim input As String = CType(Dts.Variables("FullSourcePathFileName").Value, String)
            Dim xsl As String = CType(Dts.Variables("XsltPath").Value, String)
            Dim output As String = CType(Dts.Variables("FullSourceTransformedPathFileName").Value, String)

            Try
                Dim xslt As New MvpXslTransform()
                xslt.Load(xsl)
                xslt.Transform(New XmlInput(input), Nothing, New XmlOutput(output))

                Dts.TaskResult = Dts.Results.Success
            Catch ex As Exception
                Throw
                ' Look at logging, e.g. Dts.Logging.Log()
            End Try
        End If

    End Sub

End Class

    I'm referencing the Mvp.Xml project (available on CodePlex) assembly which provides a .NET implementation of the EXSLT functions. As a bonus side-effect this means I can remove the str:split template implementation from the xsl. I've replaced the Microsoft msxml namespace declaration with the following:

    xmlns:exsl="http://exslt.org/common"

    to call the str:split function directly (no need to store it in a variable).

    The only implication I'm aware of is that I'll need to install Mvp.Xml into the GAC of the server that SSIS will be installed on (for details see here).