In this article, we will briefly explain the VS_NEEDSNEWMETADATA SSIS exception, one of the most popular exceptions that an ETL developer may face while using SSIS. Then, we will run an experiment that reproduces this error. Then, we will show how we can fix it.

What is the VS_NEEDSNEWMETADATA SSIS exception?

The keyword VS_NEEDSNEWMETADATA is a constant used in the DTSValidationStatus enum defined in the SQL Server Integration Services (SSIS). This enumerator specifies the return value of an SSIS component validation function.

As mentioned in the official SSIS documentation, the DTSValidationStatus enum constants are defined as the following:

Table 1 – DTSValidationStatus enum

In the SSIS Data Flow Component validation documentation, the VS_NEEDSNEWMETADATA value is mentioned as a return value of the Validate() function used to validate the SSIS component. They mentioned that a VS_NEEDSNEWMETADATA SSIS exception indicates that an error exists in the component metadata and that the component can repair the errors. This value indicates that the component contains the code that detects and corrects the validation problems. The component should repair the error in a call to the ReinitializeMetaData() method defined in the SSIS designer.

Reproducing the VS_NEEDSNEWMETADATA SSIS exception

In this section, we will create an Integration Services package and reproduce the exception. Noting that all experiments are running using Visual Studio 2019 and SQL Server 2019.

Let us assume that we were asked to build an SSIS package that reads from a table containing the customer information as following:

• FirstName (String, length = 50)
• LastName (String, length = 50)
• BirthYear (Integer)

First, we open Visual Studio and created a new Integration Services project. Then we add a Data Flow Task to the package Control Flow. We added two OLE DB Connection managers for the Source and destination database.

Table 2 – Data Flow task screenshot

Let’s assume that after a while, the BirthYear column in the source customer table is renamed to BirthDate, and it is still stored as an integer. As an example, 1980 is updated to 19800101 (yyyyMMdd).

We can perform those actions by executing the following query:

Now, let us try to open and execute the package. The following package validation exception is thrown with the following error message:

Error at Data Flow Task [SSIS.Pipeline]: “OLE DB Source” failed validation and returned validation status “VS_NEEDSNEWMETADATA”.

Table 3 – VS_NEEDSNEWMETADATA exception

Before fixing this error, let us right-click on the OLE DB Source component and click on “Show the Advanced Editor”.

Table 4 – Show Advanced Editor

Now, let us go to the “Inputs and Outputs properties” tab. Under the OLE DB Source Output node, we will see that the source columns metadata is persisted within the OLE DB Source component. It will not automatically change once the correspondent column is changed in the source database.

Table 5 – Advanced editor – Inputs and Outputs properties tab

How to fix the VS_NEEDSNEWMETADATA SSIS exception?

As mentioned in the first section, the VS_NEEDSNEWMETADATA SSIS exception indicates that the component contains the code that detects and corrects the validation problems. And that the component should repair the error in a call to the ReinitializeMetaData() method defined in the SSIS designer.

The ReinitializeMetaData() function is automatically called once we open the SSIS component and we navigate to the Columns tab (or the tab where the source metadata is defined).

Table 6 – OLE DB Source columns tab

After ensuring that the source metadata is updated in the OLE DB source component, let us try to re-execute the package again.

Table 7 – Package executed successfully

As shown in the image above, the package is executed successfully without making any complex configuration or changing any property. The component itself has fixed the VS_NEEDSNEWMETADATA SSIS issue.

• Note:One important thing is to make sure that your SSIS designer is not switched to the offline mode. You can check that by right-clicking on the control flow background and check if the “Work offline” property is checked or not since the offline mode may prevent the SSIS component from reinitializing its metadata

Table 8 – Check if the SSIS designer is in offline mode

Conclusion

In this article, we have explained what the VS_NEEDSNEWMETADATA SSIS exception is and when it is thrown. Then, we ran an experiment in order to reproduce this exception. Later, we showed how the SSIS component that throws this exception could fix itself by simply reinitializing its metadata.

• Author
• Recent Posts

Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies.

Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.

On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.

Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.

View all posts by Hadi Fadlallah

Latest posts by Hadi Fadlallah (see all)

• An overview of SQL Server monitoring tools - December 12, 2023
• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023