x

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.
more ▼

asked May 31, 2011 at 03:09 AM in Default

Xploring_SSIS gravatar image

Xploring_SSIS
31 2 2 2

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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
more ▼

answered May 31, 2011 at 03:19 AM

aRookieBIdev gravatar image

aRookieBIdev
2.3k 47 55 61

(comments are locked)
10|1200 characters needed characters left

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?
more ▼

answered May 31, 2011 at 04:02 AM

Xploring_SSIS gravatar image

Xploring_SSIS
31 2 2 2

pls let me know what are the tasks u are trying to use in ur package ?
May 31, 2011 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, 2011 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, 2011 at 04:58 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered May 31, 2011 at 05:09 AM

Xploring_SSIS gravatar image

Xploring_SSIS
31 2 2 2

(comments are locked)
10|1200 characters needed characters left

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?
more ▼

answered Jun 01, 2011 at 06:30 AM

Xploring_SSIS gravatar image

Xploring_SSIS
31 2 2 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x927
x378
x146

asked: May 31, 2011 at 03:09 AM

Seen: 2272 times

Last Updated: May 31, 2011 at 03:09 AM