Biml Generated Xml Source (SSIS) has Node_ID column even though the Xml has only 1 node.

Using Biml we generate SSIS packages that contain an Xml Source to get data from an Xml file.

We use a variable to get the name of the Xml file (which includes the date and time the Xml was created) and  fixed XSD schema files which specify the columns to retrieve from the Xml file for each package.

The Xml file contains 1 outer element/node named "Data". However when the package is generated it turns out that the Xml Source contains a "Data_Id" column that is not specified in the XSD schema file nor the XML file. This column can only be seen when you select Advanced Editor on the Xml source and disappears when you click OK after opening the Xml Source. 

This Data_Id column causes an error message when the package runs: "The Data_Id has no mapping to the XML schema".

We would however want to generate the packages and not have to open every single package and within each package the Xml source for it to work. 

Some googling revealed that similar "_Id" columns are created by an Xml source in SSIS automatically when the Xml contains multiple Nodes, such that these nodes can later be joined together. However, since our Xml files contain only 1 Node (Data), this Data_Id column should not be generated, but is nonetheless present in the Biml generated SSIS package.

Has anyone had this problem before and/or knows how to get rid of it?

0

Comments

1 comment
  • Hi Henk,

    I understand that when you open the advanced editor you are able to get the package to run. Would you be able to create a copy of the dtsx file BEFORE you have opened it in SSIS and rename it before.dtsx , and then create another copy after you have opened it in the advanced editor and it is now able to run, you can name that one after.dtsx. Please zip up both files and send them to support@varigence.com so we can take a look. 

    We have tried to reproduce the issue but have been unable to do so. 

    0
    Comment actions Permalink

Please sign in to leave a comment.