Deploying SSIS Package to SQL Server 2016

2
votes

I have an SSIS project in VS:

Microsoft Visual Studio Professional 2015 Version 14.0.25431.01 Update 3 Microsoft .NET Framework Version 4.6.01590 SQL Server Data Tools 14.0.61116.0 Microsoft SQL Server Data Tools SQL Server Integration Services
Microsoft SQL Server Integration Services Designer Version 13.0.1601.5

Project > Properties > Configuration Properties > Deployment Target Version > TargetServerVersion = SQL Server 2016

I deployed and validated successfully on two SQL server instances, but cannot on a third.

The chief difference I see in the three instances is this:

First (success)

Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) Jul 5 2017 23:41:17 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

Second (success)

Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) Jul 5 2017 23:41:17 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 Datacenter 6.2 (Build 9200: ) (Hypervisor)

Third (failed)

Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64) Sep 5 2017 16:12:34 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

I have been unable to track down fixes for the errors via Google, and they seem to be the same as when I target SQL Server 2014. I can post those as an edit if necessary, but my suspicion is the Sql Server installation (perhaps an issue specific to the Enterprise edition or something otherwise missing from that install) and not my package based on the above.

Here are the first and likely most relevant errors: enter image description here

1
sql-serverdeploymentssissql-server-2016
It 'failed'. But with what error? first 2 instances are developer edition whereas third one is enterprise edition. Edit your question and put up the error if possible. - Prabhat G
yes, Enterprise v. Developer is the chief difference I've seen. Let me know if the errors are helpful. They were not based on my little experience with SSIS. - jacoblambert
i found that if Deploy from within Visual Studio that this isn't a problem. For some reason I was never able to use the built wizard, although it's supposedly the same thing. So I installed SSDT on the production machine and publish from there - Zach Smith
I would suggest, if you have SQL 2016 enterprise, install SSIS there -> run your package from there(to verify) and then deploy. The error The component metadata could not be upgraded to the newer version of the component occurs when there's compatibility issue between versions. - Prabhat G
Also do raise your question here: dba.stackexchange.com - Prabhat G

1 Answers

3
votes

I found that this error goes away if you deploy from within Visual Studio instead of the wizard that is output by the build. However, I don't think you can connect to remote SQL Server instances when deploying from Visual Studio (I think that a Windows login is required).

So the solution was to install SSDT (Visual Studio 2015) on the production machine and deploy locally. This is obviously not great because you then have to move the SSDT project to the target machine every time you want to deploy (i use Git for this).

I'm sure Microsoft will come up with a fix for this eventually... Although at the moment I'm still waiting for them to release SSDT for Visual Studio 2017.

=== EDIT October 2017

SSDT is now available in Visual Studio 2017. However, I found that I still had to deploy from a locally running Visual Studio (but I can't remember why since having tried this a couple months ago from VS2017)