Custom XML destination. Modifying pipeline control to take Multiple inputs

Question

Custom XML destination. Modifying pipeline control to take Multiple inputs

mick L

SSCommitted

Points: 1526
More actions
February 1, 2010 at 9:32 am

#138564

Folks,
I am modifying the codeplex SSIS XML destination control to try and populate an xml file that has multiple sections. To populate the various sections I need several inputs (different SQL statements as oledb sources). I am not sure how to modify the overriden processinput method to allow multiple input buffers as currently it only seems to allow one datatable.
Has anyone created a custom destination control before, that takes more that one input and if so, what modifications did you make to the processinput method (and/or other methods) to allow the persistance of more than one source?
Thanks
Mick

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

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Apologies upfront Mick as I'm not directly answering your question :hehe: But, on reading that you need several sections to the document, possibly a simpler approach could be to create/import all of the pieces and then use the XML merge to merge them all to a single document, and then use the destination 'as is' in that you should then be simply exporting a single document?

I've used the merge with some success (and some tribulations, multiple namespaces were a pita for a bit).

HTH,

Steve.

mick L SSCommitted Points: 1526 More actions · Answer 2

Steve mate, Im intrigued, can you elaborate a bit? Not that versed in SSIS.

Were you able to implement the relationships within the XML structures when you merged it together?

Cheers

Mick

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Hey Mick,

Not sure what you mean exactly by 'implement the relationships within the XML structures'? If you mean something like ' i want to add a Country element named 'Australia' and then add a City ('Brisbane') element within that specific Country' then yes, you can do that. It'll require some XPath :w00t: knowledge and some level of control over your passes/control flow in the package (ie would want to either have nested loops, adding country then city (a-z) then next country etc; or a loop that adds all countries and then adds the cities then adds the houses etc).

This is a link to the base page on the XML task on MSDN. I think I linked the 2005 version, not sure what has changed to the 08 one.

Steve.

mick L SSCommitted Points: 1526 More actions · Answer 4

Folks,

The SSIS XML destination control proclaims that it can

Support multiple inputs on a single component

I have not been able to get this to work. If I add more than one oledb source to a data flow and wire it up to the control I get an error

"the destination control does not have any available inputs".

Has anyone successfully used more than one input into this control and if so can you please elaborate on how this works?

Thanks

Mick

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

Hi Mick,

Seems to work as advertised. Ran a quick test with two XML files (XML Source in the dataflow) and then a single new FileConnection as the target for the XML Destination. Dragged the output from each XML Source to the XML Destination, it asks which Input/Output you're wanting to use. Subsequently lets you specify which columns you want to bring in from each input etc etc.

Steve.

mick L SSCommitted Points: 1526 More actions · Answer 6

Thanks Steve,

Any chance you send me a screenshot of the dataflow?

Just so I know Im not going mad, will you try using two oledb sources to do the same?

Cheers

Mick

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 7

Done and done.

One thing to note - appears the XML Destination likes to consume strings rather than blobs. If you have some level of control over your OLE DB source, try to push the XML through as a string rather than an XML object (ie DOM).

HTH,

Steve.

mick L SSCommitted Points: 1526 More actions · Answer 8

Steve,

Thanks for providing the example. You inspired me to try this on another machine and it worked. Must have had a "ghost" version of the control in the toolbox.

Thank you

Mick

mick L SSCommitted Points: 1526 More actions · Answer 9

Running into the problem you mentioned with varchar(max)fields/blobs

[XML Destination [1112]] Error: System.InvalidCastException: Xml type 'List of xdt:untypedAtomic' does not support a conversion from Clr type 'BlobColumn' to Clr type 'String'.

I have tried converting the type in the SQL before handing over to the XML destination but no good. Even tried a data conversion step to change the types before inputting to the XML destination.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 10

To get around this I created views and casted the xml to varchar. I was 'lucky' though as I was able to use varchar(4000) because none of my xml docs were > 4000 char in size.

If you're mucking around in the source, you could look to override or completely overhaul the input parms and allow for true xml blob to come through as a parm?

Steve.

mick L SSCommitted Points: 1526 More actions · Answer 11

Thanks Steve,

My solution was to use stored procedures to pull each xml section and return a table variable with the required data and format.