tag:blogger.com,1999:blog-7725030.post5684455236667256804..comments2024-03-17T04:19:30.243-07:00Comments on Information Transmogrification: SSIS XML DestinationTex Hexhttp://www.blogger.com/profile/14268105680697143201noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-7725030.post-45750849234655579992009-05-28T03:24:47.823-07:002009-05-28T03:24:47.823-07:00You could also use System.Xml directly (adding ref...You could also use System.Xml directly (adding reference to System.Xml on script task):<br /><br />Imports System<br />Imports System.Data<br />Imports System.Math<br />Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper<br />Imports Microsoft.SqlServer.Dts.Runtime.Wrapper<br />Imports System.IO<br />Imports System.Xml<br />Imports System.Reflection<br /><br />Public Class ScriptMain<br /> Inherits UserComponent<br /><br /> Private rootElement As String = "root"<br /> Private rowElement As String = "row"<br /><br /> Private targetFile As String<br /> Private xmlWriter As StreamWriter<br /> Private columns As Integer()<br /> Private columnames As String()<br /><br /> Public Overrides Sub AcquireConnections(ByVal Transaction As Object)<br /> targetFile = "c:\\test3.xml" ' CType(Me.Connections.Destination.AcquireConnection(Nothing), String)<br /> End Sub<br /><br /> Private xmldoc As XmlDocument<br /> Private xmlnode As XmlNode<br /> Private xmlroot As XmlElement<br /> Private xmlelem As XmlElement<br /> Private xmltext As XmlText<br /> Public Overrides Sub PreExecute()<br /> xmldoc = New XmlDocument()<br /> xmlnode = xmldoc.CreateNode(XmlNodeType.XmlDeclaration, "", "")<br /> xmldoc.AppendChild(xmlnode)<br /> xmlroot = xmldoc.CreateElement(rootElement)<br /> xmldoc.AppendChild(xmlroot)<br /> ' set date & time<br /> xmlelem = xmldoc.CreateElement("info")<br /> xmlelem.InnerText = "DataFlow processing - " & DateTime.Now.ToString()<br /> xmlroot.AppendChild(xmlelem)<br /><br /> Dim input As IDTSInput90 = ComponentMetaData.InputCollection(0)<br /><br /> ReDim columns(input.InputColumnCollection.Count)<br /> columns = Me.GetColumnIndexes(input.ID)<br /><br /> Dim column As IDTSInputColumn90<br /> ReDim columnames(input.InputColumnCollection.Count)<br /><br /> Dim counter As Integer<br /> counter = 0<br /><br /> For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection<br /> columnames(counter) = column.Name<br /> counter = counter + 1<br /> Next<br /><br /> End Sub<br /><br /> Public Overrides Sub PostExecute()<br /> xmldoc.Save(targetFile)<br /> End Sub<br /><br /> Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)<br /><br /> While Buffer.NextRow()<br /> xmlelem = xmldoc.CreateElement(rowElement)<br /> Dim counter As Integer<br /> counter = 0<br /><br /> For Each index As Integer In columns<br /> Dim value As Object = Buffer(index)<br /> xmlnode = xmldoc.CreateNode(XmlNodeType.Element, columnames(counter).ToString(), Nothing)<br /> xmlnode.InnerText = value.ToString()<br /> xmlelem.AppendChild(xmlnode)<br /> counter = counter + 1<br /> Next<br /> xmlroot.AppendChild(xmlelem)<br /> End While<br /> End Sub<br />End ClassAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7725030.post-23989014381883487422008-12-09T04:00:00.000-08:002008-12-09T04:00:00.000-08:00Yes, this is true and I know the destination from ...Yes, this is true and I know the destination from that company. <BR/><BR/>However, most XML destinations I use an XSLT later on anyway, so this is method is perfectly okay for me.Tex Hexhttps://www.blogger.com/profile/14268105680697143201noreply@blogger.comtag:blogger.com,1999:blog-7725030.post-83716538100183218572008-12-03T19:58:00.000-08:002008-12-03T19:58:00.000-08:00An easier solution may be to use a third party XML...An easier solution may be to use a third party XML Destination Adapter such as the one from Keelio Software (http://www.keelio.com). You'll get much more flexibility then what you are doing with the code sample you provided.Matthttps://www.blogger.com/profile/08950931685549205092noreply@blogger.com