Convert Excel to XML

Here is a free tool

http://www.softinterface.com/DL/DL_UserData_Proc.ASP

The requirement is I have to do it in SSIS

If you have a well formatted xls document then there is no reason to convert it to xml all you need to do is import the xls document into ssis.

Well users will drop it on a share and I SSIS package will need to pick up the Excel sheet that the users dropped.

If you have excel, open the file and save it as in xml format and upload it, everything should work.

Can I not upload the Excel and then convert it to XML. Because I already propose that and they want to save it to a excel file

I tried this VB code but it does not work

         Dim dt As New DataTable()
        Dim ds As New DataSet
        dt.Columns.Add("Currency", String.Empty.GetType())
        dt.Columns.Add("Legal Entity", String.Empty.GetType())
        dt.Columns.Add("Account id", String.Empty.GetType())
        dt.Columns.Add("Movement", String.Empty.GetType())
        dt.Columns.Add("Value date", String.Empty.GetType())
        dt.Columns.Add("Narration", String.Empty.GetType())
        dt.Columns.Add("Counterparty", String.Empty.GetType())
        ds.Tables.Add(dt)
        Dim FILE_NAME As String = "C:\Users\anthonio.booysen\xmltest.xml"
        Dim objWriter As New System.IO.StreamWriter(FILE_NAME)
        objWriter.Write(ds.GetXml)
        objWriter.Close()

I manage to get this to work. Took a long time but it works

I manage to get this to work. Took a long time but it works