2

I have a basic package that includes a couple of Exec SQL Tasks and a Data Flow task. It was developed using Visual Studio 2019 (latest edition as of now), in package deployment model. The execution in VS works perfectly but fails when ran by SQL Agent (within SQL Server 2012 Standard) because of this error

"Package migration from version 8 to version 6 failed with error 0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.". "

What I've tried:

1- I've already read through all other answers to this same question: I went through Properties and then Changed Deployment Version from 2019 to 2012. In fact, I get this confirmed:

enter image description here

2- I went through XML and ensured that there is no such thing as Version 8, and it is only in V6 3- I recreated the whole thing again using Visual Studio but this time I started with the deployment target set as 2012.

Please help. Thank you

LearnByReading
  • 1,813
  • 4
  • 21
  • 43
  • Are you sure you deployed the package as a 2012 package though? Changing the project's setting after deploying, and not redeploying, wouldn't fix the issue – Thom A Aug 28 '20 at 15:00
  • @Larnu This is a file deployment model, so simply deleting the old file and replacing it with the new one fixes the issue. So this is not the case here. – LearnByReading Aug 28 '20 at 15:04
  • Why not use SSISDB? It's vastly better than the file system deployment method for so many reasons. And if you *are* using the file system deployment method, you need to ensure you rebuild the file after changing the project's settings. – Thom A Aug 28 '20 at 15:09
  • When you run the package, can you add the `/rep eiw` flag and dump out the first two lines - just to verify it is the 2012 engine firing up? Something along these lines `Microsoft (R) SQL Server Execute Package Utility Version 14.0.3335.7 for 32-bit` – billinkc Aug 28 '20 at 15:12
  • @billinkc thanks for your reply. Yes, it is the 2012 engine firing up (SQL Server 2012's SQL Agent is running it) – LearnByReading Aug 28 '20 at 15:23
  • Could you add the flag when running with the Agent?? – LearnByReading Aug 28 '20 at 15:23
  • Also, SQL Server 2012 is 110 which uses PackageFormatVersion 6, SQL Server 2014 is 120 PackageFormatVersion 8. – billinkc Aug 28 '20 at 15:24
  • @billinkc it is: "SQL Server Execute Package Utility Version 11.0.5634.1 for 64-bit " – LearnByReading Aug 28 '20 at 18:40

1 Answers1

0

I thought I'd answer in case someone else is struggling: the issue is due to a fundamental design flaw within VS (it is stupid how they designed it without considering this). In essence, I was using connection types (drivers) that are not supported in the earlier versions. Connection types rarely get version upgrades (say for instance SQLN11 vs. 10). All you get when you get the target server is a different XML schema, it does not change the connection type to a SQL 2012 friendly version.

I changed the connection types manually by recreating the whole package using VS 2012. It worked. But if you have earlier versions of the connection types, you don't have to do this, just choose the earlier ones if it allows you to.

LearnByReading
  • 1,813
  • 4
  • 21
  • 43