SSIS Error: VS_NEEDSNEWMETADATA

Question

I'm currently updating all of our ETLs using Visual Studio 2015 (made in BIDS 2008) and redeploying them to a new reporting server running on SQL Server 2016 (originally 2008R2).

While updating one of the ETLs and trying to run on the new server I got this error:

---

The package execution failed. The step failed.

Sometimes it also produces this error:

Source: Load Fact Table SSIS.Pipeline Description: "Copy To Fact Table" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

---

I've tried deleting and re-adding the OLEDB Destination, connection strings and opened up the column mappings to refresh the meta data. I also recreated the whole data flow task but I'm still getting the same error.

The package runs fine on my local machine.

UPDATE:

I started taking the package apart and running only pieces of it to try and narrow down which part was failing. It seemed to be failing on loading into the staging table but I couldn't find out why.

I eventually decided to just try and re-create the whole thing. After re-creating the entire package, still no luck. The picture below is from the event viewer on the server itself but it didn't give me any new information.

Package error from event viewer

Thickening

How is this package being executed? I.e., manually through SSMS, via a scheduled job, programmatically, etc – Balaam

I'm executing it through a SQL Server Agent job. The SQL Service has permissions to the folder as well. – Thickening

What does the package execution details have to say? (Reports -> Standard Reports -> All Executions, drill down into failed execution). – Balaam

For some reason when I click on Reports it only shows 'Custom Reports' and I clicked on that and there aren't any. – Thickening

This would be on the package itself in SSMS, on the server it's deployed to (Integration Services Catalogs/SSISDB/Your Env Name/Projects/Your Solution/Package/Package Name -> right click) – Balaam

Oh I see, we are still using the package deployment model so our packages are in the file system. Is there another way to access that report? – Thickening

if it runs ok in your development environment, then the problem isn't with the package, it's with the scheduled job on the server. Try recreating that. – Extrinsic

To ask a possibly stupid question, have you tried redeploying since you got it working locally? – Balaam

@Beth, I just recreated the job like you suggested but I'm still getting the same error – Thickening

@mikeTheLiar, yes I've been redeploying every time I make a change. It's worked locally the entire time somehow. – Thickening

It seems like the server has a cached instance of the package it's using instead of the updated one. Try renaming your package and creating a new job with the new package name and see if that works. – Extrinsic

@Beth, a good idea but same error! Thank you for all of the ideas so far! – Thickening

all I can recommend at that point is to cut the package down until it succeeds, then add the next step that fails. – Extrinsic

@Beth, thank you, that helped me narrow it down when I was debugging. Could you please repost my answer? – Thickening

@MikeG This is how I found the problem. There was a warning right before the error that explained why the package failed the metadata validation. – Diligent

The source and destination are strongly typed. I answered below, but my issue was resolved by setting the type in my "Select" source query to match the data type of the mapped destination. Select CONVERT(NVARCHAR(255),firstname + lastname) as Fullname from... – Harmattan

Answer 1

I finally found the issue and here's how I did it.

Because the error messages I was getting from SSMS weren't very insightful I first opened up my remote desktop and logged into the server. Then I went to Administrative Tools>Event Viewer and then Windows Logs>Application to see if the failed event would provide greater detail.

It didn't give me much still.

The next step I took was to run the package from the command line because the messages should be more verbose. Opened up cmd, changed directory to the one my package was in and then...

DTEXEC /FILE YourPackageName.dtsx

Finally, the error message here showed a missing column in the tables the package was trying to write to. I added those columns and voila!

Thickening

Answer 2

I have tried all the solutions provided above and the other sites. Nothing worked.

I got a suggestion from my friend Which worked for me.

Here are the steps:

1. Right click on the Source/Target Data flow component.
2. Go to Advanced Editor -> Component Properties
3. Find ValidateExternalMetadata and set it to False.

Try your luck. This is a pathetic issue and left me clueless for 2 days.

Faucher

This solution worked for me too. But why should this be necessary? – Nathanielnathanil

2020 and this has still NOT changed in VS-2019. just a FYI. – Redbreast

Done the trick for me (unfortunately still an issue) – Otero

I followed this advise and now the error message was more verbose. Thanks! – Shack

Answer 3

As stated in comments,
if it runs ok in your development environment, then the problem isn't with the package, it's with the scheduled job on the server. Try recreating that.

If that doesn't work,
It seems like the server has a cached instance of the package it's using instead of the updated one. Try renaming your package and creating a new job with the new package name and see if that works.

If that doesn't work, all I can recommend at that point is to cut the package down until it succeeds, then add the next step that fails.

Sounds like from your solution the development environment is more forgiving of schema updates than the deployed solution. Glad you were able to resolve, eliminating clutter helps.

Extrinsic

Answer 4

I had the same problem and my issue was a difference between two environments, the same field in the same table once was written with a capital and once not. So the name was the same, but with this small difference (e.g. isActive vs IsActive).

This came from a refactoring effort, where we used VS database publish that did not updated the field name.

Lyn

This was what caused the problem for me. – Teapot

Thanks for the suggestion- this was my problem as well. You might find that setting ValidateExternalMetadata: false as per the above answer will also fix the issue. – Isochromatic

I had a capitalization issue with an ODBC connection to Db2. I had a subquery that was pulling directly from a table/column with names in all caps, the outer query had Title case, I could do a preview, but the Data Flow Task wouldn't run – Dissimilate

Answer 5

Have you tried deleting and re-creating the source? When I get this I can generally modify OK any object that has the error but have to delete and rebuild the paths between them, however sometimes I have to delete everything in the data flow and re-create it.

Contrite

I tried re-creating the source and when nothing else worked I ended up re-creating the entire package but it still failed with the 'The package execution failed. The step failed.' message. – Thickening

Answer 6

A Proxy for SSIS Package Execution should be created under the SQL Server Agent. You should then change your job step (or steps) to Run As the Proxy you've created.

I had your same problem some time ago and the proxy fixed it.

Forgive me if you've already tried this.

Grimes

Answer 7

It is very common to get that message when 2 columns in the source file are being inserted into the same field of the table.

i.e.

My text file has twice "neighborhood" (same label for different columns) and my table has "neighborhood" and "neighborhoodb" (notice the "b" at the end). The import will try to import both text columns into the field "neighborhood" and ignore the "neighborhoodb" field, it will fail with the "VS_NEEDSNEWMETADATA" error.

Bustamante

Answer 8

Re-creating the job worked for me. Some cached version of the job may have been causing the VS_NEEDSNEWMETADATA error. The package was executing correctly but it was failing, when it was executed by an agent job.

Cyprinodont

Answer 9

This ended up being a permissions issue for me. The OLE DB Source was using a stored procedure that selected from a SQL view. This view joined to a table in another database and unfortunately the proxy account the SQL Agent job step was running the package under did not have SELECT permission to the table in that database. This is why the package ran fine in Visual Studio but not from a job when deployed to the server. I found the root cause of the error by taking the SELECT statement out of the stored procedure and putting it directly in the Source Query box of the OLE DB Source control which caused it to finally return the 'SELECT permission denied' error message. This error was apparently hidden from SSIS since the proxy account DID have execute permission on the stored procedure.

Window

Answer 10

It works for me after changing the ValidateExternalMetadata to false. I was transferring the data from MSSQL database to MySQL database. Changed "ADO NET Destination".

Lewan

You are right @Vega Thanks for heads up. – Euridice

Answer 11

You may need to strongly type your Source Query.

Example:

If your DestinationDB has a FullName field Nvarchar(255) and in your source query you have

select firstname + lastname as FullName from...

Try this:

Select CONVERT(NVARCHAR(255),firstname + lastname) as Fullname from...

So if you are going from db to db and both are nvarchar(255) I don't have this issue, but if you are concatenating fields in your query specify the data type and length.

Harmattan

Answer 12

This error can also occur when an entire SSIS project needs to be redeployed rather than just one of the packages (for VS versions that allow deployment of a single package in a multi-package project), particularly when a project connection has been changed or added. For example, if you've added or removed columns from a flat-file project connection. In that case, you need to deploy the entire project to push out the updated project connection properties. This can be true even if the project only has one package in it. In VS Solution Explorer, rather than click on the package name to deploy, select the bolded project name at the top, and then click deploy.

Ardellaardelle

Answer 13

The same problem here but nothing worked for me. I got the same VS_NEEDSNEWMETADATA error message after removing some columns from data source. The error occured even if I removed completely the Data Flow task from the package. That showed me something is wrong with the entire project deployment model. So I rebuild the solution and that finally helped.

Excrescency

Answer 14

I had the same issue after adding a column to OLE DB data source, I've tried many solution including deleting the data flow and recreate again but still not worked. Below are the solution that work at last:

1. Click Extensions > SSIS > select 'Work Offline'
2. Set 'Delay Validation=TREU' for all connections that use variable.
3. Go to OLE DB Source/ Destination and any object in data flow which show warning sign , right click 'Show Advance Editor'
4. Go to Component Properties, set 'ValidateExternalMetadata=False'
5. Unselect 'Work Offline' in item#1 then run the package

Ampere

Answer 15

ODBC connection to IBM Db2 changing an existing query

I was seeing this when I tried to update queries that were used to get data from an ODBC connection to IBM Db2. (I'm not sure if it was ODBC or Db2 that cause this). Turning off ValidateExternalMetadata only made things worse.

There are 2 fixes;

1. Recreate the Data Source
2. Change the date source to a table and close the dialog (this force SSIS to get the new Metadata). Then re-open and change back to Query and input your updated query (once again forcing a Metadata refresh).

Dissimilate