Reading and Writing XML File in SSIS

Hi,

I have been assigned a task to read data from a Oracle table and XML file. There is a ID column which joins both the dataset. The output should be written to a XML file. I have planned to do this using SSIS. I dont have a SQL database assigned for this project hence can't load the XML data to SQL table. Is there anyway to connect the 2 datasets and generate XML file on the fly.

I'm just a starter in SSIS and have never used XML in SSIS so far. Could you please guide me through on how to do this? Any help is greatly appreciated.

Thanks.

asked May 31 '11 at 03:09 AM in Default

Xploring_SSIS
31 ● 2 ● 2 ● 2

(comments are locked)

4 answers:

Hello,

You can use an XML source and and Oledb source to connect to the xml file and oracle respectively.use the appropriate transformations like joins,xml transformation etc.The resulting dataset can be written to a XML only using a script component. The script component requires some c# coding to write the data in to a xml file. In ssis you do not have a XML destination task directly.

Hope this was useful.

Thanks, Kannan

answered May 31 '11 at 03:19 AM

aRookieBIdev
2.1k ● 25 ● 43 ● 48

(comments are locked)

Thanks for the quick response Kannan.

My XML file looks like this, 1234

My Office www.myoffice.com addr1 addr2 Here 12354 012345 0 call@myoffice.com</email 11000 ABC moreInfo

XYZ 5 now AT NONE XYZ NONE

Hence it results in multiple output which I'm not able to join and connect with Oracle. Any thoughts?

answered May 31 '11 at 04:02 AM

Xploring_SSIS
31 ● 2 ● 2 ● 2

pls let me know what are the tasks u are trying to use in ur package ?

May 31 '11 at 04:15 AM aRookieBIdev

I tried using XML source, OLEDB source, Union all,script component. I tried using Merge join too it too didn't work out :(

May 31 '11 at 04:41 AM Xploring_SSIS

For Mege Join you have to have both sources sorted on the key used in the Merge Join. So if you if your sources are sorted then in advanced settings you can set that the source is sorted or you will have to put a sort transformation prior the Merge Join.

May 31 '11 at 04:58 AM Pavel Pawlowski

(comments are locked)

Pavel, I'm not able to output all the data from the XML file as it has multiple outputs. It doesn't allow me to connect OLEDB source with multiple output from XML.

answered May 31 '11 at 05:09 AM

Xploring_SSIS
31 ● 2 ● 2 ● 2

(comments are locked)

Hi,

I managed to create the SSIS. When I ran the same, the data from XML file is not being read and inserted as NULL.It doesn't throw up any error too. Am I missing something? Could you please help me resolving this?

answered Jun 01 '11 at 06:30 AM

Xploring_SSIS
31 ● 2 ● 2 ● 2