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