Convert SQL data or Flat File or Csv File to XML ?

Question

Convert SQL data or Flat File or Csv File to XML ?

Harry-657490

SSC Veteran

Points: 280
More actions
May 3, 2009 at 10:01 am

#204006

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Harry-657490 SSC Veteran Points: 280 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

Have a look here for an example of how it can be done.

http://blogs.conchango.com/jamiethomson/archive/2006/07/11/4209.aspx

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Harry-657490 SSC Veteran Points: 280 More actions · Answer 4

Harry-657490

SSC Veteran

Points: 280

May 4, 2009 at 12:04 pm

#988266

Thanks Phil...

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

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/

msumangupta Old Hand Points: 359 More actions · Answer 6

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

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

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.

VyomK3 SSC Journeyman Points: 98 More actions · Answer 7

@msumangupta:

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!

Phil Parkin SSC Guru Points: 246985 More actions · Answer 8

If you type

Me.Connections.

does Intellisense give you any options at all?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

VyomK3 SSC Journeyman Points: 98 More actions · Answer 9

All that intellisense is giving me are bunch of functions with following options:

Equals

GetHashCode

GetType

ReferenceEquals

ToString

Screenshot:

Alan Burstein SSC Guru Points: 61141 More actions · Answer 10

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