Post reply

Dynamic Mapping for XML in SSIS?

  • 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.

  • December 29, 2013 at 10:45 pm

    #1676291

    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)

  • December 30, 2013 at 3:58 am

    #1676337

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 30, 2013 at 8:08 am

    #1676391

    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)

  • December 30, 2013 at 8:39 am

    #1676394

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 30, 2013 at 1:38 pm

    #1676466

    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

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

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