Convert SQL data or Flat File or Csv File to XML ?
-
Hi Experts,
I am new to SQL Server , i was practicing some transformation in SSIS, i can convert XML data into SQL Data..
could any one please tell me how to Convert SQL Data or Flat File or Csv file to Xml ...
Thanks in Advance...
regards
Hari
-
not as easily;
xml is supposed to be self describing...so an xml element can contain the name, the data type, and the value; it can contain much more information than those basic 3 elements as well.
raw text/csv doesn't do that, so it's not as easy...it's pretty much just the values, and it usually requires some conversion, where you import everything as varchar, and change some fields to int,datetime, etc.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell,
Could you tell me in More Brief step by step, As per u r reply i understand that its not easy to do that,
that means we need to code into XML is that rite...
Thank you well in advance...
Regards
Hari
-
Have a look here for an example of how it can be done.
http://blogs.conchango.com/jamiethomson/archive/2006/07/11/4209.aspx
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Thanks Phil...
-
Harry (5/3/2009)
Hi Experts,I am new to SQL Server , i was practicing some transformation in SSIS, i can convert XML data into SQL Data..
could any one please tell me how to Convert SQL Data or Flat File or Csv file to Xml ...
Thanks in Advance...
regards
Hari
Check CozyRoc Template Task. You can generate arbitrary type of text documents with it.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Hear is one way that i can suggest to everyone for converting Flat File to XML using the Script Component "Destination" in the data flow task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Private targetFile As String
Private xmlWriter As IO.StreamWriter
Private rootElement As String = "Root"
Private rowElement As String = "Row"
Private columns As Integer()
Private Columnnames As String()
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
targetFile = CType(Me.Connections.DesFile.AcquireConnection(Nothing), String)
End Sub
Public Overrides Sub PreExecute()
xmlWriter = New IO.StreamWriter(targetFile, False)
xmlWriter.WriteLine(FormatElement(rootElement))
Dim input As IDTSInput90 = ComponentMetaData.InputCollection(0)
ReDim columns(input.InputColumnCollection.Count)
columns = Me.GetColumnIndexes(input.ID)
Dim column As IDTSInputColumn90
ReDim Columnnames(input.InputColumnCollection.Count)
Dim counter As Integer
counter = 0
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
Columnnames(counter) = column.Name
counter = counter + 1
Next
End Sub
Public Overrides Sub PostExecute()
xmlWriter.WriteLine(FormatElement(rootElement, True))
xmlWriter.Close()
End Sub
'Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Dim column As IDTSInputColumn90
' Dim rowType As Type = Row.GetType()
'Dim columnValue As Reflection.PropertyInfo
'With xmlWriter
'.Write(FormatElement(rowElement))
' For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
'columnValue = rowType.GetProperty(column.Name)
' Try
' .Write(FormatElement(column.Name) + columnValue.GetValue(Row, Nothing).ToString() + FormatElement(column.Name, True))
'Catch ex As Exception
' Write(CInt(FormatElement(column.Name) + "" + FormatElement(column.Name, True)))
'End Try
' Next
' .WriteLine(FormatElement(rowElement, True))
' End With
' End Sub
Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
While Buffer.NextRow()
'Dim values As New System.Text.StringBuilder
xmlWriter.Write(FormatElement(rowElement))
Dim Counter As Integer
Counter = 0
For Each Index As Integer In columns
Dim Value As Object = Buffer(Index)
xmlWriter.Write(FormatElement(Columnnames(Counter).ToString()) + Value.ToString() + FormatElement(Columnnames(Counter).ToString(), True))
Counter = Counter + 1
Next
xmlWriter.WriteLine(FormatElement(rowElement, True))
End While
End Sub
Private Function FormatElement(ByVal elementName As String) As String
Return FormatElement(elementName, False)
End Function
Private Function FormatElement(ByVal elementName As String, ByVal closingTag As Boolean) As String
Dim returnValue As String
If closingTag Then
returnValue = "</"
Else
returnValue = "<"
End If
returnValue += elementName + ">"
Return returnValue
End Function
End Class
Copy the above script and place it in the design script tab in the script component. I hope the only change that you have to make in this script is the Connection name that you give for your XML Destination Connection Manager in the Script Task. In the Script Above The Name that I used was "DesFile". If you just Replace the Name for what you have used, the FlatFile will be converted in to the XML File.
-
Sorry for bumping this old thread, but believe me when I say I have looked every other options before doing that.
I think the code you provided for converting Text File Source to XML seems to be very good. But I have a question in following lines:
targetFile = CType(Me.Connections.DesFile.AcquireConnection(Nothing), String)
In this line, I am getting an error even if I replace DesFile with the name of my connection manager. Can you please help on what to write in place of DesFile to get the conversion from text source file to XML, to work?
The name of XMLConnection that I have created is conTargetXML but replacing DesFile with conTargetXML is not working. The error I am getting is:
Error2'conTargetXML' is not a member of 'SC_f8187669a2e147ab9d856ec04791a173.vbproj.Connections'.C:\Users\Vyom\AppData\Local\Temp\3\SSIS\9862cb61317841b598cea9e0e195e776\main.vb2428sc_f8187669a2e147ab9d856ec04791a173
I would Really appreciate if you can reply. Hoping to get a positive response!
-
If you type
Me.Connections.
does Intellisense give you any options at all?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
All that intellisense is giving me are bunch of functions with following options:
Equals
GetHashCode
GetType
ReferenceEquals
ToString
Screenshot:
-
Phil Parkin (5/3/2009)
Have a look here for an example of how it can be done.http://blogs.conchango.com/jamiethomson/archive/2006/07/11/4209.aspx
Blog is down :crying:
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply