SSIS Error: "ODBC Source" failed validation and returned validation status "VS_NEEDSNEWMETADATA"

Question

I have an SSIS project that I have not had any issue with for the last year. I can execute the unchanged packages without issue currently from Visual Studio 2019 and as a SQL Agent Job in SSMS.

However, if I open the data flow ODBC source in the Data Task, I am prompted to update the Metadata on the Output columns. Whether I select yes or no and make no other changes to the package, I receive the below error on execution:

"ODBC Source" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

The only change that I can think that may have caused this error is I updated from Visual Studio 2017 to Visual Studio 2019 on the server. I do use VS 2019 on my local computer and it is having the same issue.

The ODBC Source is using a SQL Anywhere 17 32-bit driver.

I have tried the following and continue to receive the error on execution:

• Recreated the data flow task, the ODBC connection manager, and the package.
• Ran the package using DTEXEC /FILE.
• Set ValidateExternalMetadata to False on the ODBC Source
• Confirmed the External and Output columns on the Data Source have the same DataType

EDIT: I created a new Project in VS 2017 with the same Data Flow Task. I could run and edit the ODBC Source without any Metadata errors on execution. I opened the project in VS 2019 and as soon as I opened the ODBC Source, I was prompted to update the metadata. Then when executing, I received the "VS_NEEDSNEWMETADATA" error. Any ideas on correcting this in VS 2019?

Pomfret

Answer 1

Try to change the ODBC Source ValidateExternalMetadata property to False, for more information you can check Matt Masson's (former program manager of SSIS) answer in the following link:

• ValidateExternalMetadata property, what exactly does this do?

Also, make sure that you have selected the right TargetServerVersion property from the project configuration:

• How to change TargetServerVersion of my SSIS Project

Heterodoxy

Thanks Hadi. I tried this as mentioned in my original request and still get an execution error in VS 2019. Oddly enough, if I use VS 2017 to change ValidateExternalMetadata to false, save, and close the package, I can then open the same package in VS 2019, edit/execute the ODBC Source without any metadata errors. Seems like a lot of work to modify all of my working packages in VS 2017 so that I can modify them in VS 2019. – Pomfret

@Pomfret is TargetServerVersion set to the right SQL Server version? – Heterodoxy

SQL Server 2017. Is that the problem? – Pomfret

@Pomfret what is the installed SQL Server version – Heterodoxy

Version 14.0.1000.169 – Pomfret