How can i generate a huge xml from SSIS?
Hi, I'm trying to generate an XML file through SSIS and i'm following the below mentioned steps to generate it: In SQL Server: 1. I created a proc called “spGenerateXML” that contains my SELECT FOR XML statement. In SSIS: 1. I created a Package level string variable called "XMLVariable" 2. In Connection Manager, I created a New .Net Provider/SQL Client Data Provider Connection called "ADODB" 3. In Connection Manager, I created a New File Connection called "XMLFile" 4. On Control Flow tab a) Added Execute SQL Task b) Added Script Task c) Connected Execute SQL Task (parent) to Script Task (child) 5. Edited Execute SQL Task, General section a) Changed Result Set to "XML" b) Set Connection to "ADODB" c) Set SQLStatement to "EXEC spGenerateXML" 6. Edited Execute SQL Task, Result Set section a) Clicked Add button b) Set Result Name to 0 c) Set Variable Name to "User::XMLVariable" 7. Edited Script Task, Script Section a) Set ReadOnlyVariables to "XMLVariable" b) Click Design Script button c) Replaced "Main" Subroutine with: Public Sub Main() Dim XMLString As String = Nothing Dim fs As IO.StreamWriter = Nothing Dim strfilename As String = DirectCast(Dts.Connections("XMLFile").AcquireConnection(Dts.Transaction), String) XMLString = Dts.Variables("XMLVariable").Value.ToString.Replace("<ROOT>", "").Replace("</ROOT>", "") 'XMLString = Dts.Variables("XMLVariable").Value.ToString fs = New IO.StreamWriter(strfilename, False) fs.Write(XMLString) fs.Close() Dts.TaskResult = Dts.Results.Success End Sub Though the above approach did help me in generating small files but failed when i tried generating huge XML files. Could you please guide me how can i generate huge XML file through SSIS 2005?
April 8th, 2011 10:36am

How huge is huge? Here is a Script Example of generating an XML file: http://microsoft-ssis.blogspot.com/2010/12/flexible-xml-destination.html It's C#, but you can translate it to VB.net. Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 10:41am

Thanks a lot for your reply. I followed the steps mentioned but could not make the package work as i'm completely new to VB.net. I tried converting the C# code to VB.net but got lot of errors which i could not fix. Following is the VB code which i tried but it doesn't work. ' Flexible script that creates a XML document ' using the SSIS columns as nodes. Imports System Imports System.Data Imports System.Reflection ' Added Imports System.Xml ' Added Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _ Public Class ScriptMain Inherits UserComponent ' Variable that contains the XML document Private textWriter As XMLTextWriter ' Start of XML document Public Overrides Sub PreExecute() MyBase.PreExecute() ' Create a new XML document and use the filepath in the connection as XML-file textWriter = New XMLTextWriter(Me.Connections.xmldocument.ConnectionString.ToString, Nothing) ' Start writing the XML document: textWriter.WriteStartDocument() ' Create root element <root> textWriter.WriteStartElement("ROOT") End Sub ' Close of XML document Public Overrides Sub PostExecute() MyBase.PostExecute() ' Close root element: </root> textWriter.WriteEndElement() ' Stop writing the XML document textWriter.WriteEndDocument() ' Close document textWriter.Close() End Sub ' Method that will be started for each record in you dataflow Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' Row type to get the value of a column Dim rowType As Type = Row.GetType Dim columnValue As String = "" ' Create row element: <row> textWriter.WriteStartElement("ROW") ' Loop through all columns and create a column element: <col1>value</col1><col2>value</col2> For Each column As XMLTextWriter In Me.ComponentMetaData.InputCollection(0).InputColumnCollection ' Use the SSIS column name as element name: <col1> textWriter.WriteStartElement(column.Name) ' Get column value, will fail if null Try columnValue = rowType.GetProperty(column.Name).GetValue(Row, Nothing).ToString Catch As System.Exception ' Default value for null values: "null", "" or null columnValue = "null" Finally textWriter.WriteString(removeForbiddenXmlChars(columnValue)) End Try ' Close column element: </col1> textWriter.WriteEndElement() Next ' Close row element: </row> textWriter.WriteEndElement() ' Output the number of processed rows. 103 = RowsWritten Me.ComponentMetaData.IncrementPipelinePerfCounter(103, 1) End Sub ' Remove forbidden chars that could damage your XML document Private Function removeForbiddenXmlChars(ByVal columnValue As String) As String Return columnValue.Replace("&", "&").Replace("<", "<").Replace(">", ">") End Function End Class I get errors in Script at the places which i have marked in BOLD letters. And also i would like to mention that i have put the query which i was using in SP in the ole db source and getting the xml in a variable which is of type DT_NTEXT. Please let me know how to proceed from here. Thanks, Deepti Deepti
April 8th, 2011 11:17am

And also the XML file could be around 30 to 40 MB.Deepti
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 11:19am

I added a VB.net example for you on the site: http://microsoft-ssis.blogspot.com/2010/12/flexible-xml-destination.html Let me know if it works for you (and if it performce for your large files). Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
April 8th, 2011 2:31pm

Just out of my curiosity: If a small XML file worked, what prevented a large (btw 30-40 Mb is not a large file at all, I would call anything larger than 2 GB large) from being generated? I bet there was an error. What error? PS: I am asking because you may had been on the right path nearing the solution delivery.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
April 8th, 2011 2:40pm

Try this:http://dwhanalytics.wordpress.com/2011/03/30/ssisoutput-table-value-as-xml/ Thanks http://dwhanalytics.wordpress.com/
April 9th, 2011 8:44pm

Hi SSISJoost, Thanks a lot again and i tried using the VB code present in the above mentioned website but it gives me error at the same places. I tried understanding that VB code and i guess it forms an xml from the columns present in the table mentioned in the OLE DB source and hence it might be throwing error. (not sure though :)). I'm forming the XML in the desired format in a query which i'm using in OLE DB Source and getting into a variable called XMLVariable which becomes the column of OLE DB Source of DT_NTEXT type. Brief information about the query: This query forms a little complex XML by going through a table which has got around 150K records and is as mentioned below: SELECT(SELECT(SELECT A,B, (A + B) AS 'AB',C, (SELECT DISTINCT ([D]+'|'+[E]) FROM X WHERE A = X.A FOR XML PATH('SC'), TYPE), (SELECT DISTINCT F,G,H,I FROM Y LEFT OUTER JOIN Z ON Y.P= Z.P WHERE Y.A= X.A FOR XML PATH('MD'), TYPE), (SELECT DISTINCT J,K FROM U LEFT OUTER JOIN V ON U.A= V.A WHERE V.A= P.A FOR XML PATH('TD'), TYPE) FROM P JOIN PC ON PC.A= P.A WHERE P.A IN (SELECT DISTINCT A FROM PC WHERE ID<>980673) FOR XML PATH('FD'), TYPE) FOR XML PATH('RH'), TYPE) AS XMLVARIABLE Thanks, Deepti Deepti
Free Windows Admin Tool Kit Click here and download it now
April 10th, 2011 5:24am

Hi, Yeah even i thought so but after little research on net, i felt that the string variable might be causing a problem as it might be getting truncated. I dont have the exact error as i have to execute package for arnd 4 hrs and then it throws the error. But the error is something like exception has been thrown by the target of an invocation. This is generally the error which we get in SSMS when we try to generate a very huge XML. Deepti
April 10th, 2011 6:44am

Hi ArthurZ, Yeah even i thought so but after little research on net, i felt that the string variable might be causing a problem as it might be getting truncated. I dont have the exact error as i have to execute package for arnd 4 hrs and then it throws the error. But the error is something like exception has been thrown by the target of an invocation. This is generally the error which we get in SSMS when we try to generate a very huge XML. Deepti
Free Windows Admin Tool Kit Click here and download it now
April 10th, 2011 6:44am

Hi AKBI, I tried that approach too but i get the following error in the xml file which is created by the package: - <ROOT> <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?> </ROOT> Package runs successfully but when i open the generated file then i see this instead of XML. Deepti
April 10th, 2011 9:02am

Try ADO.Net data source for the SQL Task instead of existing OLEDB connection.Thanks http://dwhanalytics.wordpress.com/
Free Windows Admin Tool Kit Click here and download it now
April 10th, 2011 2:33pm

I think there are some differences between the .net framework for the XmlTextWriter. I made it in SSIS 2008 and you are using 2005. regards, JoostPlease mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
April 10th, 2011 3:16pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics