SSIS package job fails with VS_NEEDSNEWMETADATA error

Question

Hey I have SSIS project deployed in SQL Server SSIS. I also have a scheduled job which runs the deployed SSIS proeject from SQL Server. (See the screenshot) Now when this job runs, (which is basically runnign the dtsx package) I get an error email which says that

Load Fact Staging Table Failed on - Reporting Data Warehouse with following Error Messages:

"Fact Destination" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

I understand that this error can be solved if I open the dtsx package on the server using visual studio, I can fix it by right-click on the data source and then select edit, and then YEs. In my case since this only fails on production server, I do not have access to visual studio. So my question how can I fix this error by just using the dtsx package

I am using SQL Server 2012

Yeah, you are right. In this case I cannot find any alter scripts executed on any source or destination scripts. So debugging this is being hard — imba22
As the error says, the tables have changed since the package was created and now it needs new metadata. You can't edit it by hand, even though it's XML. You need to use SSDT to open it and refresh any sources/destinations that have an error mark. Better yet, copy the database changes to whatever database was used for developing that package, edit it, and then publish it again — Panagiotis Kanavos
You don't need change scripts to find what changed. You can backup the target database, restore it to your development server and edit the package. Or you can use SSDT's own Schema Comparison tool to find the changes and apply them to your development database — Panagiotis Kanavos
You can't do any of this without SSDT. That's a standalone installation by the way, you don't need to buy Visual Studio to use it. — Panagiotis Kanavos
So I guess there is no way to debug this on prod server without visual studio and SSDT installed — imba22

Alexander Volok Alexander Volok · Accepted Answer · 2019-02-15T11:36:35

VS_NEEDSNEWMETADATA - this error means that underlying schema of tables that are involved in package was changed by someone, for instance column or table was renamed etc.

The dtsx package is XML file internally. If you are skillful enough you can adjust it in Notepad++ and resubmit a package.

But I would still recommend to do it in BIDS/SSDT, since big change that you will end up with inconsistencies in your data flow lineages.

Another workaround is to revert changes on database side that are the reason of such issue. Perhaps restore of backup side by side with further comparison of involved fact table

If SSMS is available, you can relatively easy find some traces on where to search by using Schema Change History report:

SSIS package job fails with VS_NEEDSNEWMETADATA error

1 Answers