question

Xploring_SSIS avatar image
Xploring_SSIS asked

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.
ssisoraclexml
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

4 Answers

·
aRookieBIdev avatar image
aRookieBIdev answered
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
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Xploring_SSIS avatar image
Xploring_SSIS answered
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 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?
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aRookieBIdev avatar image aRookieBIdev commented ·
pls let me know what are the tasks u are trying to use in ur package ?
0 Likes 0 ·
Xploring_SSIS avatar image Xploring_SSIS commented ·
I tried using XML source, OLEDB source, Union all,script component. I tried using Merge join too it too didn't work out :(
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
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.
0 Likes 0 ·
Xploring_SSIS avatar image
Xploring_SSIS answered
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.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Xploring_SSIS avatar image
Xploring_SSIS answered
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?
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.