4

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Hslew
  • 69
  • 1
  • 2
  • 7

1 Answers1

5

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:

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 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. – Hslew Sep 02 '19 at 18:28
  • @Hslew is `TargetServerVersion` set to the right SQL Server version? – Hadi Sep 02 '19 at 20:42
  • SQL Server 2017. Is that the problem? – Hslew Sep 02 '19 at 22:20
  • @Hslew what is the installed SQL Server version – Hadi Sep 03 '19 at 19:46
  • Version 14.0.1000.169 – Hslew Sep 03 '19 at 20:09