Post reply

SSIS 2005 XML file.

  • March 1, 2012 at 3:38 am

    #251630

    Hello every one,

    I have a question and I am not sure if this can be done using SSIS 2005 or not. please help me to correct If I am wrong in assuming so.

    Little Background: We have 50 customers and they all send one XML file every week with all the updates they have for that week. we need to load these files in to their corresponding customer table in our Database. We have 50 Databases one for each customer. Each database has this customer table in to which we need to load this XML file. All destination customer tables has exact same structure but each XML file we get from each customers have their own structure. So, All XML files have different structure.

    Requirement: Load all these XML files in to their corresponding Tables using an SSIS package.

    I want this SSIS package to read all these XML files one by one (please note each XML file has a different structure than the other) from a source folder and load it in their corresponding destination customer tables which are located in 50 different Databases (remember all destination customer tables has exact same structure).

    We need only one SSIS package. We do not want to build one package for each business. Also, say if incoming XML file has 10 fields and destination customer table has 15 fields, then they should be load the extra fields as null.

    Also, we thought of having a mapping table for each business with all source columns and destination columns.

    So, every time we execute this SSIS package, it should read the source columns from XML file, lookup for the match in the source column of mapping table and should figure it out the destination column in to which it should load data in to. The number of columns in the incoming XML file from our customers can change in the near future. So, we thought of having this mapping table and if something changes then we just have to input the new column name and the corresponding destination column in this mapping table assuming SSIS will automatically pick this change when it is executed.

    Any thoughts on how this can be done and if we can't why can't?

    Thanks in advance

  • March 1, 2012 at 8:39 am

    #1453684

    The differing schemas is a problem because the pipeline in a data-flow is set at design-time and cannot differ at run time.

    You mention that they are different schemas in the files. Can you expand on that. Different names of fields, different types, different column counts?

    We might be able to work around column counts but the others are a complication..

    CEWII

  • March 5, 2012 at 11:07 pm

    #1455112

    Hi Ivan,

    The standard Data Flow Task supports only static columns definition. Therefore you have to create a separate data flow for each XML file layout type. If you want to avoid this you have to construct packages at runtime, which requires complicated programming with the SSIS API.

    If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task and it supports dynamic columns at runtime. It also supports definition of mapping dictionary between your source and destination component, just like you have stated in your requirement. All this functionality can be used without any programming required.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 3 posts - 1 through 2 (of 2 total)

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