4

A package with a Script Component that runs fine on SSDT (Visual Studio 2017) does not run when deployed to SQL Server 2016. It throws a ComponentVersionMismatchException.

So, other packages work fine. This one has a data flow with a Script Component destination. When I first started developing the project, I hadn't adjusted the target from SQL 2017 down to SQL 2016. I developed everything and got it to run from SSDT. I'm using a .ispac file to deploy. When deployed, it's throwing the ComponentVersionMismatchException. Here's what I've tried to get a script component to run:

  1. Change the target to SQL 2016. Failed.
  2. Make a minor change and ensure that the script is recompiled. Failed
  3. Ensure that Visual C# 2015 is selected. Failed.
  4. Make a duplicate of the package, and delete the script component. This succeeded.
  5. In the duplicate, just add a new empty script component as destination. Failure again.
  6. Delete the data flow task and rebuild with empty script component. Failed.
  7. Create a package completely from scratch and recreate the data flow with the same source and empty script component. Failed.
  8. I tried creating a new project entirely, set the target to SQL 2016, and created a similar dataflow just with a table as source and an empty script component as destination. Still failed.

I would expect that a brand new project targeting SQL 2016 with a script component that does nothing should be able to execute on the server. I'm rather lost at this point.

Hadi
  • 36,233
  • 13
  • 65
  • 124
user2712166
  • 81
  • 1
  • 5
  • 2
    *"I would expect that a brand new project targeting SQL 2016 with a script component that does nothing should be able to execute on the server. I'm rather lost at this point."* No, it won't If you've created a script targetting a SQL Server 2017 box then it'll use more recent versions of .Net framework which SQL Server 2016 won't be able to use. Even if that script "does nothing" the requirement for that .Net Framework version still exists. – Thom A Apr 12 '19 at 15:39
  • When I created the new project entirely (step 8), I told it to target SQL 2016. I created a script component from scratch after the project is pointing to SQL 2016. My script components say they're using Visual C# 2015. Are you saying that I need to adjust the .NET Framework version elsewhere? – user2712166 Apr 12 '19 at 15:55

4 Answers4

1

Try to deploy directly from Visual Studio instead of using ispac. It may be similar to: https://feedback.azure.com/forums/908035-sql-server/suggestions/32898370-deploying-single-ssis-package-to-sql-2016-from-ssd

Piotr Palka
  • 3,086
  • 1
  • 9
  • 17
  • I would do that if it would work. In VS2017, it's throwing an error trying to connect to the server if I try to directly deploy the project. If I deploy via the ispac, it'll deploy. – user2712166 Apr 15 '19 at 16:33
  • When it's loaded directly, it gives the error "could not load file or assembly 'Microsoft.SqlServer.Management.IntegrationServicesEnum, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG)) (mscorlib)" – user2712166 Apr 16 '19 at 14:07
  • From [msdn](https://social.msdn.microsoft.com/Forums/en-US/2c063b57-8571-41e7-8a95-3ce4a105a5e3/unable-to-deploy-ssis-after-vs-2017-update-1580?forum=sqlintegrationservices):Sorry for the issue. This is actually a regression in VS 15.8. Please install latest SSDT 15.8, which contains the fix for the issue. https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017#ssdt-for-vs-2017-standalone-installer – Piotr Palka Apr 16 '19 at 14:58
  • Yes, i installed SSDT 15.9 on VS 2017 and then redeployed after having correctly set Target to SQL 2016 and that fixed the same issue that this original post was about – PBMe_HikeIt May 23 '19 at 14:22
0

SSIS Integration Services projects are not backward compatible therefore if you deploy packages authored in VS 2017 it requires the adjustment of the target from SQL 2017 to SQL 2016.

Arthur
  • 1,441
  • 1
  • 13
  • 17
0

I totally agree with @Larnu comments, the issue is not only related to the Target Server version. Even if you select SQL Server 2016 as target server, the Script Component is created using a newer version of 4.6 which is the one related to Visual Studio 2015/SQL Server 2016. You may be able to solve this issue by installing the same version of .Net framework on the deployment server.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • The server I'm deploying to and the visual studio install are on the same box. Is there a way to set the target framework within the script component itself other than the line saying visual C# 2015 on the script component? – user2712166 Apr 15 '19 at 16:30
0

Given what Piotr said AND that I had already updated to the latest version of the VS shell/SSDT for VS2017, I tried installing SQL Server Data Tools for VS2015.

Visual Studio 2015 was able to deploy directly to the (local) server and the packages deployed from the VS2015 version did not exhibit the execution issues that the ones from VS2017 did.

Given that SSDT for VS2019 has been released, it might be worth trying that version out in the future.

Looks like it is a still unfixed bug in SSDT for VS2017.

user2712166
  • 81
  • 1
  • 5