Script File:

Overview

Script Task to transform an XML document using XSLT configuration file. It providers following parameters:

IsTransformVariable – if true, transformation file path is contained in package variable. Otherwise it is file connection manager (Boolean).
TransformVariable – transformation file path package variable (String).
Transform – transformation file connection manager (connection).
IsSourceVariable – if true, source file path is contained in package variable. Otherwise it is file connection manager (Boolean).
SourceVariable – source file path package variable (String).
Source – source file connection manager (connection).
IsTargetVariable – if true, target file path is contained in package variable. Otherwise it is file connection manager (Boolean).
TargetVariable – target file path package variable (String).
Target – target file connection manager (connection).

Requirements

Depends on System.Xml.Xsl.XslCompiledTransform for XSLT transformation.

Depends on CozyRoc Script Task Plus component for implementing parameters user interface.

Library references

System
System.Data
Microsoft.SqlServer.ScriptTask
Microsoft.SqlServer.ManagedDTS
CozyRoc.SSISPlus
System.XML

ScriptMain

' Microsoft SQL Server Integration Services Script Task ' ' Copyright (c) 2008 CozyRoc LLC ' ' Permission is hereby granted, free of charge, to any person ' obtaining a copy of this software and associated documentation ' files (the "Software"), to deal in the Software without ' restriction, including without limitation the rights to use, ' copy, modify, merge, publish, distribute, sublicense, and/or sell ' copies of the Software, and to permit persons to whom the ' Software is furnished to do so, subject to the following ' conditions: ' ' The above copyright notice and this permission notice shall be ' included in all copies or substantial portions of the Software. ' ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, ' EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES ' OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND ' NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT ' HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, ' WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING ' FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR ' OTHER DEALINGS IN THE SOFTWARE. Imports System Imports System.ComponentModel Imports System.Collections Imports System.Xml Imports System.Xml.Xsl Imports Microsoft.SqlServer.Dts.Runtime Imports CozyRoc.SqlServer.SSIS.Attributes ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <SortProperties(New String() { _ "IsTransformVariable", "TransformVariable", "Transform", _ "IsSourceVariable", "SourceVariable", "Source", _ "IsTargetVariable", "TargetVariable", "Target"})> _ <FilterProperties("GetProperties")> _ <ValidateProperties("Validate")> _ 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() Dim result As Integer Dim transformFile As String Dim sourceFile As String Dim targetFile As String Dim xslt As New XslCompiledTransform Try ' Get parameters. transformFile = IIf( _ Me.IsTransformVariable, _ GetVariable_(Me.Transform), _ GetConnectionFile_(Me.Transform)).ToString() sourceFile = IIf( _ Me.IsSourceVariable, _ GetVariable_(Me.Source), _ GetConnectionFile_(Me.Source)).ToString() targetFile = IIf( _ Me.IsTargetVariable, _ GetVariable_(Me.Target), _ GetConnectionFile_(Me.Target)).ToString() ' Transform XML. Call xslt.Load(transformFile) Call xslt.Transform(sourceFile, targetFile) result = Dts.Results.Success Catch ex As Exception result = Dts.Results.Failure Call Dts.Events.FireError( _ 0, _ String.Empty, _ ex.Message, _ String.Empty, _ 0) End Try Dts.TaskResult = result End Sub ' Main ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Returns list of current properties based on current state. Public Function GetProperties() As String() Dim result As New ArrayList ' Base properties. Call result.Add("IsTransformVariable") Call result.Add(IIf(Me.IsTransformVariable, "TransformVariable", "Transform")) Call result.Add("IsSourceVariable") Call result.Add(IIf(Me.IsSourceVariable, "SourceVariable", "Source")) Call result.Add("IsTargetVariable") Call result.Add(IIf(Me.IsTargetVariable, "TargetVariable", "Target")) Return CType(result.ToArray(GetType(String)), String()) End Function ' GetProperties ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Validate(ByRef errMessage As String) As Boolean Dim result As Boolean Dim props As Object() Try If String.IsNullOrEmpty(Me.Transform) Then Throw New ApplicationException("Please specify Transform.") End If If String.IsNullOrEmpty(Me.Source) Then Throw New ApplicationException("Please specify Source.") End If If String.IsNullOrEmpty(Me.Target) Then Throw New ApplicationException("Please specify Target.") End If result = True Catch ex As Exception result = False errMessage = ex.Message End Try Validate = result End Function 'Validate #Region "Properties" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" Transform")> _ <Description("Specifies if Transform property references package variable.")> _ <RefreshProperties(RefreshProperties.All)> _ Public Property IsTransformVariable() As Boolean Get IsTransformVariable = m_isTransformVar End Get Set(ByVal value As Boolean) If m_isTransformVar <> value Then m_isTransformVar = value m_transform = String.Empty End If End Set End Property ' IsTransformVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" Transform")> _ <Description("Specifies variable containing transformation (XSLT).")> _ <Variable()> _ Public Property TransformVariable() As String Get TransformVariable = m_transform End Get Set(ByVal value As String) m_transform = value End Set End Property ' TransformVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category(" Transform")> _ <Description("Specifies transformation (XSLT).")> _ <Connection("FileConnectionType")> _ Public Property Transform() As String Get Transform = m_transform End Get Set(ByVal value As String) m_transform = value End Set End Property ' Transform ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Source")> _ <Description("Specifies if Source property references package variable.")> _ <RefreshProperties(RefreshProperties.All)> _ Public Property IsSourceVariable() As Boolean Get IsSourceVariable = m_isSourceVar End Get Set(ByVal value As Boolean) If m_isSourceVar <> value Then m_isSourceVar = value m_source = String.Empty End If End Set End Property ' IsSourceVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Source")> _ <Description("Specifies variable containing source to transform.")> _ <Variable()> _ Public Property SourceVariable() As String Get SourceVariable = m_source End Get Set(ByVal value As String) m_source = value End Set End Property ' SourceVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Source")> _ <Description("Specifies source to transform.")> _ <Connection("FileConnectionType")> _ Public Property Source() As String Get Source = m_source End Get Set(ByVal value As String) m_source = value End Set End Property ' Source ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Target")> _ <Description("Specifies if Target property references package variable.")> _ <RefreshProperties(RefreshProperties.All)> _ Public Property IsTargetVariable() As Boolean Get IsTargetVariable = m_isTargetVar End Get Set(ByVal value As Boolean) If m_isTargetVar <> value Then m_isTargetVar = value m_target = String.Empty End If End Set End Property ' IsTargetVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Target")> _ <Description("Specifies variable containing target to store transformation.")> _ <Variable()> _ Public Property TargetVariable() As String Get TargetVariable = m_target End Get Set(ByVal value As String) m_target = value End Set End Property ' TargetVariable ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' <Category("Target")> _ <Description("Specifies target to store transformation.")> _ <Connection("FileConnectionType")> _ Public Property Target() As String Get Target = m_target End Get Set(ByVal value As String) m_target = value End Set End Property ' Target #End Region ' Properties #Region "Internals" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private ReadOnly Property FileConnectionType() As String() Get FileConnectionType = New String() {"FILE"} End Get End Property ' FileConnectionType ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetVariable_(ByVal varName As String) As String Dim result As String Dim vars As Variables Call Dts.VariableDispenser.LockOneForRead(varName, vars) Try result = vars(varName).Value.ToString() Finally Call vars.Unlock() End Try GetVariable_ = result End Function ' GetVariable_ ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Function GetConnectionFile_(ByVal connName As String) As String Dim result As String result = Dts.Connections(connName).AcquireConnection(Nothing).ToString() GetConnectionFile_ = result End Function ' GetConnectionFile_ #End Region ' Internals #Region "Attributes" Private m_isTransformVar As Boolean Private m_transform As String Private m_isSourceVar As Boolean Private m_source As String Private m_isTargetVar As Boolean Private m_target As String #End Region ' Attributes End Class ' ScriptMain

Transform XML Task

Overview

Requirements

Library references

ScriptMain

PASS Summit '10