none
SSIS Package gets "failed validation and returned validation status "VS_NEEDSNEWMETADATA""

    Question

  • This is similiar to others, but, my destination tables are the same.  The only difference is the XML files that I use as the source.  I am mapping a single XML file to 8 tables based on the XSD.  If I build the Data Flow based on file 680.xml, it works.  When I go into the XML Source definition and change to 707, you get the following errors:

    Error at Import680 [SSIS.Pipeline]: input column "id" (626) has lineage ID 1646 that was not previously used in the Data Flow task.

     

    Error at Import680 [SSIS.Pipeline]: "component "dbProduct" (516)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

    Now, if you go into each of the desination data flow objects and click OK ("Restore Invalid Column References Editor"), the new file works fine including loading all of the data.  You switch back to file 680.xml, and you get the same error again.

    Thoughts?

    Tuesday, March 16, 2010 12:36 PM

All replies

  • When you switch files and look at the Data Flow in the designer, which components have "x" in them?  The XML source does, yes?

    If so, then change the file to 680 and repair the data flow so it works.  Right-click on the arrow coming out of the source and select "Edit".  Select the "Metadata" tab, and copy out the information there (column names and data types).

    Now switch the file to 707 and repair the data flow so it works.  Repeat the above to view the metadata.  Compare the 680 metadata with the 707 - there will be something different.

    SSIS can not process two XML files with different metadata using the same data flow.  You'll have to pre-process the XML with an XSLT or other method to make both files have the same metadata, or have two data flows - one per file "type".  Make sense?
    Todd McDermid's Blog
    Tuesday, March 16, 2010 12:49 PM
  • Thanks for the fast response...no, the X is only in the desitination, each one of them...I am wondering if I need to set the file name dynamically and then run some sort of refresh command just like pressing the OK button in the Invalid Column References editor...
    Tuesday, March 16, 2010 4:06 PM
  • Are you changing which table the data gets sent to when you change your input file?
    Todd McDermid's Blog
    Tuesday, March 16, 2010 5:04 PM
  • No I am not...I have the same XML format from multiple countries in different files...they go to the same base tables...I am just trying to change the source and not make any changes to the destination.
    Tuesday, March 16, 2010 5:50 PM
  • Ah.  I understand our miscommunication.

    Please go through the steps I outlined above for collecting the metadata from the two versions of your files, and report back what those columns/data types are.


    Todd McDermid's Blog
    Tuesday, March 16, 2010 6:30 PM
  • Here is one of them:

    For GB:
    "Name" "Data Type" "Precision" "Scale" "Length" "Code Page" "Sort Key Position" "Comparison Flags" "Source Component"
    "group_Id" "DT_UI8" "0" "0" "0" "0" "0" "" "XML Source"
    "name" "DT_WSTR" "0" "0" "255" "0" "0" "" "XML Source"
    "properties_Id" "DT_UI8" "0" "0" "0" "0" "0" "" "XML Source"


    For US:
    "Name" "Data Type" "Precision" "Scale" "Length" "Code Page" "Sort Key Position" "Comparison Flags" "Source Component"
    "group_Id" "DT_UI8" "0" "0" "0" "0" "0" "" "XML Source"
    "name" "DT_WSTR" "0" "0" "255" "0" "0" "" "XML Source"
    "properties_Id" "DT_UI8" "0" "0" "0" "0" "0" "" "XML Source"

    what is interesting as well is if I move teh file from one directory to another and then go into the package and change the location in the Data Flow Editor for the source, the same thing happens.
    Tuesday, March 16, 2010 8:38 PM
  • That doesn't make any sense.  Doing what you're doing (if the files are as identical as they look) shouldn't cause SSIS to complain about changes to metadata.

    If you configure the data flow to work for 680, then rename the 707 file in order to overwrite the 680 file (so the 707 data is now "named" 680) - do you get the metadata warnings?
    Todd McDermid's Blog
    Tuesday, March 16, 2010 10:42 PM
  • Are you changing which table the data gets sent to when you change your input file?
    Todd McDermid's Blog

    I am doing this. What I'm trying to achieve is loop through a folder looking for all csv files (with different number of fields -most common to all files) and load the data into tables that bear the source file's name. Is this possible using SSIS? Please advice.
    Tuesday, December 21, 2010 4:45 PM