Command-line Building of a Single Solution for Multiple Versions of SQL Server

  • vanceother

    SSC Journeyman

    Points: 84

    I posted this same article to the Continous Integration forum but thought the SSDT forum might be appropriate as well...

    I'm trying to setup some continuous integration for building an ISPAC file for both SQL Server 2014 and SQL Server 2016 from a single solution. I don't want to have to maintain two different versions of the solution nor do I want to have to keep changing the TargetServerVersion project property. I'm using SSDT for Visual Studio 2017. I've setup two configurations in Configuration Manager: one with a TargetServerVersion of 2014 and one for 2016.

    When I call devenv from the "Develop command prompt" command-line and use the 2016 configuration, I get the window dialog box that states: You are about to change the TargetServerVersion property...Click Yes, No

    There doesn't seem to be a way to automate this response. I admit my build knowledge is very limited so I could be missing something in what SSDT has to offer to help in my situation.

    I've read numerous articles about how SSIS does not lend itself to CI very well and is a rather pain getting MSBUILD to automate the build process without VS/SSDT. At this point, I've resigned myself to having to install SSDT on the build server at least. But, now this issue of building multiple SQL Server version ISPAC files from the same solution via a command-line is my next hurdle.

    Thanks in advance for your help.

    Mark

  • Phil Parkin

    SSC Guru

    Points: 243477

    vanceother - Tuesday, October 16, 2018 4:29 AM

    I posted this same article to the Continous Integration forum but thought the SSDT forum might be appropriate as well...

    I'm trying to setup some continuous integration for building an ISPAC file for both SQL Server 2014 and SQL Server 2016 from a single solution. I don't want to have to maintain two different versions of the solution nor do I want to have to keep changing the TargetServerVersion project property. I'm using SSDT for Visual Studio 2017. I've setup two configurations in Configuration Manager: one with a TargetServerVersion of 2014 and one for 2016.

    When I call devenv from the "Develop command prompt" command-line and use the 2016 configuration, I get the window dialog box that states: You are about to change the TargetServerVersion property...Click Yes, No

    There doesn't seem to be a way to automate this response. I admit my build knowledge is very limited so I could be missing something in what SSDT has to offer to help in my situation.

    I've read numerous articles about how SSIS does not lend itself to CI very well and is a rather pain getting MSBUILD to automate the build process without VS/SSDT. At this point, I've resigned myself to having to install SSDT on the build server at least. But, now this issue of building multiple SQL Server version ISPAC files from the same solution via a command-line is my next hurdle.

    Thanks in advance for your help.

    Mark

    It's been a while since I played with this stuff.

    Which method (PoSh, C#, T-SQL, other) of deployment are you using and what happens if you attempt to deploy a 2014 ispac to a 2016 server?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • vanceother

    SSC Journeyman

    Points: 84

    We use the project deployment model.  We sometimes use SSMS to deploy.  But, I've started using the Deployment Wizard directly because IT is installing the 2017 version of SSMS on 2016 server installations.  And, I don't have access to the 2016 version of SSMS.  And, I know that if you use the 2017 version of SSMS to deploy a 2016 package to a SQL Server 2016 box, the deployment is successful but the package fails.

    I was under the impression running a 2014 package on SQL Server 2016 would not work because of a previous issue (the issue as stated above when using SSMS 2017 to deploy a 2016 package to a 2016 server).  But that issue and my current issue tied to this topic are different but I was thinking they were the same.  So, I was able to use the 2016 Deployment Wizard (bypassing SSMS) to deploy the 2014 package to SQL Server 2016.  The deployment was successful.  The package successfully ran.  So, this would indicate I don't need to maintain a 2016 build.

    I did notice that when deploying the 2014 package to SQL Server 2016, the deployment process ran a lot longer (the step dealing with 'protection level') than when deploying a 2016 version of the same package to SQL Server 2016.  I wonder if that's because the deployment wizard was converting it on the fly much like what happens when you change the TargetServerVersion at the project level in Visual Studio.

    Thanks Phil for answering my question with a question.  Sometimes that's all that's needed.  🙂

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply