SSIS - XML Destination

Question

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

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

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 1

There aren't any XML destination adapters available out-of-the-box.

It's possible that there are 3rd party adapters available on the web for purchase.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gerbera SSCommitted Points: 1715 More actions · Answer 2

Thanks Koen...

Do you have any inputs on the below:

1.I need to read data from Flat file, convert into XML string and load into XML column in a SQL table.

My Data Flow:

Flat file -->temp table--> For XML query--> XML string stored in SSIS string variable--> insert variable into XML column

Is there anyway to convert flat file data to XMLstring without using temp table?

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 3

Unless you code the XML yourself in a script component, I don't think so.

But I would skip the step where you read the temp table out with FOR XML, put it into a variable and then insert it into the table.

I would insert it directly in the table using an INSERT clause followed by a SELECT ... FOR XML.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gerbera SSCommitted Points: 1715 More actions · Answer 4

you suggest me to read individual fields from file, put in variables using Script and then use INSERT clause in Execute SQL task

Am i correct?

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 5

No, I would read the file and write it entirely to a staging table.

Then I would read this staging table with a FOR XML clause and insert the results in the final destination table.

No variables involved.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gerbera SSCommitted Points: 1715 More actions · Answer 6

Koen,

Is it not possible without storing flat file data in Staging table?

Thanks.

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 7

Gerbera (3/15/2012)
Koen,
Is it not possible without storing flat file data in Staging table?
Thanks.

Yes, if you construct the XML yourself in a script task.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gerbera SSCommitted Points: 1715 More actions · Answer 8

The Fields from which I need to construct the XML is coming from flat file..

So i need to use them in SCRIPT COMPONENT as destination inside a dataflow and construct an XML?

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 9

Yup.

I guess there are plenty of sources on the net on how to create XML in .NET.

p.s.: my guess is that you'll be begging for the temp table 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gerbera SSCommitted Points: 1715 More actions · Answer 10

SSCommitted

Points: 1715

March 15, 2012 at 7:37 am

🙁 let me look into the available options;

Gerbera SSCommitted Points: 1715 More actions · Answer 11

Yes 🙂 As you say, i have requested for temp table !