Azure Devops Build SSIS task

0
votes

I have created an SSIS package using VS 2017 and added the project to Azure Devops. I am trying to setup a build task in Azure Devops using the SQL Integration Service add in. Everything I am doing is setup on my machine which includes the Agent Pool etc. When trying to setup parameters for the SSIS build the Devenv Version selection only gives me 12 and 14. VS2017 doesn't appear. As a result it appears that when I do a build the incorrect version on devenv is used and the build (even though it doesn't say its failed) fails.

The version the build is using is C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Devenv.com but should be using C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE>Devenv.com

How do I get to DEVENV config parameter to include the Hosted VS2017 version?

The following web page gives an idea of what I am trying to achieve: -

http://chamindac.blogspot.com/2018/09/build-and-deploy-ssis-with-azure-devops.html

1
ssisazure-devopsazure-pipelines
Looking at our DevOps, we have a Powershell script that looks like this :&"${env:ProgramFiles(x86)}\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\devenv.com" "$(Build.SourcesDirectory)\SSIS\MySSISProject.dtproj" /rebuild $(BuildConfiguration). Not posting as an answer because I don't know if it even works. – Nick.McDermaid
Thanks Nike. The code does run VS 2017 however, I would need to tell the build to use V2017 in the first place which I am unable to as it doesn't appear in the DEVENV Config dropdown build parameter setting. So if there is code that determines which versions of VS are available that would possibly tell me why its not finding VS2017 to populate the selection parameter for DEVENV. – user1790316
Maybe I'm missing something but there There is no devenv config dropdown in a powershell step. It just runs a command line. – Nick.McDermaid
The powershell step doesn't have one but the SSIS build deploy one does. I have changed it now use powershell and it builds. Unfortunately when I try to deploy now using powershell scripts I have another issue. $integrationServices = New-Object "Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices" $sqlConnection New-Object : Could not load file or assembly 'Microsoft.SqlServer.Dmf.Common, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The file is in the C:\Windows\assembly for that version – user1790316
I believe we are using ISDeploymentWizard.exe to deploy rather than the SSIS API, again through powershell. – Nick.McDermaid

1 Answers

0
votes

I ended up using a powershell script instead. The script has one line to run the build. &"${env:ProgramFiles(x86)}\Microsoft Visual Studio\2017\Professional\Common7\IDE\devenv.com" "C:\Users\me\Downloads\vsts-agent-win-x64-2.140.0_work\2\s\POC_SSIS.sln" /rebuild

I place the code in a .ps1 file and put it into the root of my repo. I then used the Powershell config to reference the file and it worked.

I did a similar thing for the deployment where I used : - ISDeploymentWizard /S /SP:C:\Users\me\Downloads\vsts-agent-win-x64-2.140.0_work\2\s\POC_SSIS\bin\Development\POC_SSIS.ispac /DS:serverName /DP:/SSISDB/POC_SSIS/POC_SSIS