Dynamic Mapping for XML in SSIS?

Question

Post reply

Dynamic Mapping for XML in SSIS?

Karthiart

Ten Centuries

Points: 1070
More actions
December 29, 2013 at 1:26 am

#302740

Hi All,
I have a doubt on loading XML file to an SQL table.
I have 2 types of source xml files. one xml file with 10 columns and other xml file with 8 columns.
In SSIS, I am creating a mapping with xml (source contains 10 columns) to sql table.
Now, whether I can use the same package and same mapping for the other xml file which contains 8 columns.?
In short, Dynamic mapping is possible in xml & ssis?
Kindly provide your solution on this.
Regards,
Karthik.
Regards,
Karthik.
SQL Developer.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 1

Let me "bump" this one for you, Karthik. I'm interested in an answer to this general question, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246988 More actions · Answer 2

In short, Dynamic mapping is possible in xml & ssis?

Not out of the box.

In the case you've mentioned, it's probably easiest to set up two data flows which separately handle the different file types.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 3

Phil Parkin (12/30/2013)
In short, Dynamic mapping is possible in xml & ssis?
Not out of the box.
In the case you've mentioned, it's probably easiest to set up two data flows which separately handle the different file types.

Thanks, Phil... I was very interested in this because of very slowly maturing project that I've been working on. I'm not there yet but I've been toying with the idea that SQL Server will build an "edge table" (a full blown "Adjacency List" table) behind the scenes that could be rapidly interogated using a simple conversion to "Nested Sets". I have it so that, without any prior knowledge of the tags or structure for a given "simple" multi-level XML document with both entities and elemental data, I can actually generate the T-SQL XQuery to produce a "flattened table". It's got some problems that I'm still (occasionally, I don't work with XML much) trying to hammer out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246988 More actions · Answer 4

Jeff Moden (12/30/2013)
Phil Parkin (12/30/2013)
In short, Dynamic mapping is possible in xml & ssis?
Not out of the box.
In the case you've mentioned, it's probably easiest to set up two data flows which separately handle the different file types.
Thanks, Phil... I was very interested in this because of very slowly maturing project that I've been working on. I'm not there yet but I've been toying with the idea that SQL Server will build an "edge table" (a full blown "Adjacency List" table) behind the scenes that could be rapidly interogated using a simple conversion to "Nested Sets". I have it so that, without any prior knowledge of the tags or structure for a given "simple" multi-level XML document with both entities and elemental data, I can actually generate the T-SQL XQuery to produce a "flattened table". It's got some problems that I'm still (occasionally, I don't work with XML much) trying to hammer out.

That sounds like a very interesting project.

But as you've no doubt gathered by now, SSIS has not been designed to natively accommodate dynamic sources and targets. There are various techniques to get round this, depending on the specific requirement. Some are simple, some ... not.

What you are describing would fall into the 'not' category!

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Karthiart,

If you only have two files like this, I would recommend the above, as it's simpler.

However, if you have multiple XML files missing attribute tags depending on the source, but match otherwise, it shouldn't really matter. A missing attribute tag should simply become NULL in the datastream, which is what I would typically understand to be a column from your description.

What seems to be the particular concern here? Can you give us a sample of the XML files in question and how/where they differ?

Am I just not understanding what's going wrong here?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA