SQL SERVER – SSIS – Package Error: “ODBC Source” Failed Validation and Returned Validation Status “VS_NEEDSNEWMETADATA”

As a part of my consulting, I see many clients who use SSIS. In this blog we would learn how to fix error “ODBC Source” failed validation and returned validation status “VS_NEEDSNEWMETADATA”.

My client was using SSIS to transform the data from MySQL to SQL Server for reporting purpose. One fine day they found that SSIS package was failing with an error.

SQL SERVER - SSIS - Package Error: "ODBC Source" Failed Validation and Returned Validation Status "VS_NEEDSNEWMETADATA" ssis-err-001

“ConnectionToSQL” failed validation and returned validation status “VS_NEEDSNEWMETADATA”.

Since I have helped them in the past, they contacted me to seek help on SSIS. Even though it is not my expert area, I agreed to get engage with them and learn something new. I search for the error on the internet and found that this is “Needs New Metadata”. But what metadata? Well, its metadata about the schema of the source or destination table. If the schema that is being obtained from the database has changed, the data source will fail with the VS_NEEDSNEWMETADATA error. Generally, it happens when we alter the table by adding a new column or removing an existing column.

WORKAROUND/SOLUTION

As explained earlier, this is mostly caused due to a schema change of source or destination data sources. SSIS is schema bound and any change in table schema needs new metadata to be populated to SSIS as well. The solution which worked for my client was to just right-click on the data source and then select edit. It automatically prompted us with a question asking if it should fix the metadata. Choosing “yes” solved the problems.

Based on my search on the internet, I found a few more solution. Sharing them here as a single place to fix such error:

  1. Another thing to watch out for is the case sensitivity of the column names referred in package vs tables. Based on my internet search, one of them had the same error due to not having the same case.
  2. It can also happen when mappings are incorrect or there are ambiguous columns in the file. Maybe the column name is defined twice.
  3. If none of above help, then we need to get verbose logging of the package. To do that, you can run the package via command line using DTExec. (dtexec Utility)
DTEXEC /FILE PackageName.dtsx

This should give you more information and should point to the column which is having the problem.

Reference: Pinal Dave (https://blog.SQLAuthority.com)

, , , ,
Previous Post
SQL SERVER – Error: 45207 – SQL Server Managed Backup to Microsoft Azure Cannot Configure the Database Because a Container URL was Either not Provided or Invalid
Next Post
SQL SERVER – Event ID: 10028 – SQL Server Distributed Replay Client – DCOM was Unable to Communicate with the Computer

Related Posts

2 Comments. Leave new

  • Sabikah Mukhi
    April 18, 2019 1:43 am

    Amazing! Worked for me

    Reply
  • Sabikah Mukhi
    April 18, 2019 1:50 am

    My isse was that my Package was working fine on DEV server but once deployed on QA, it was giving this error . I had added a new column and on one server , the column name was in CAPITAL letters while on one it was in small letters. Worked perfectly for me Thanks

    Reply

Leave a Reply

Menu