New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

VS2017 Hosted Agent: SQL Server Data Tools #4362

Closed
OrestTkachuk opened this Issue May 19, 2017 · 15 comments

Comments

Projects
None yet
8 participants
@OrestTkachuk

OrestTkachuk commented May 19, 2017

I've found closed issue 4028, but could you please provide path to SQL Server Data Tools? I've tried following configuration for SqlPackage capability:

  • C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe
  • C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130
  • C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC
  • C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe
  • C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin

Build fails with error message: "Unable to find the location of Dac Framework (SqlPackage.exe) from registry on machine TASKAGENT9-0001".

@mvvsubbu

This comment has been minimized.

Member

mvvsubbu commented May 22, 2017

We have a different issue for the same, I am closing this issue, please follow this issue: #4338 for updates

@bryanmacfarlane

This comment has been minimized.

Member

bryanmacfarlane commented May 22, 2017

I added a cmdline line task with:
cmd: dir
arguments: /s sqlpackage.exe
(advanced)working dir: C:\

I ran the build against the VS2017 queue (under options):

It's output was:

==============================================================================
Task         : Command Line
Description  : Run a command line with arguments
Version      : 1.1.3
Author       : Microsoft Corporation
Help         : [More Information](https://go.microsoft.com/fwlink/?LinkID=613735)
==============================================================================
dir /s SqlPackage.exe
 Volume in drive C has no label.
 Volume Serial Number is 2C9B-8253
 Directory of c:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130
03/01/2017  04:48 PM           143,040 sqlpackage.exe
               1 File(s)        143,040 bytes

Does that help you?

@bryanmacfarlane

This comment has been minimized.

Member

bryanmacfarlane commented May 22, 2017

@OrestTkachuk - closing this again. I just wanted to illustrate how you can use the system itself to answer questions like that. hth.

@OrestTkachuk

This comment has been minimized.

OrestTkachuk commented May 22, 2017

@bryanmacfarlane thanks for your response. As I mentioned before, I tried 'c:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130' path and the result was the same. I am waiting for the resolution of the #4338.

@bryanmacfarlane

This comment has been minimized.

Member

bryanmacfarlane commented May 22, 2017

@OrestTkachuk - did you pick the VS2017 queue on the options tab? The hosted pool queue only has 2015 which would be why it didn't find it.

@OrestTkachuk

This comment has been minimized.

OrestTkachuk commented May 22, 2017

@bryanmacfarlane - I'm using Hosted VS2017 agent queue. I've attached configuration from the options tab and user, system capabilities from agent configuration.

  • agent configuration
  • user capabilities
  • system capabilities png
@bryanmacfarlane

This comment has been minimized.

Member

bryanmacfarlane commented May 22, 2017

@OrestTkachuk - OK. SqlPackage is there per my dir /s output. Also, don't rely on capabilities as sqlpackage isn't registered as a capability. Have you tried to call it directly to prove it? I believe the task isn't finding it via registry (issue you reported) which doesn't mean it isn't there :) You question was where is it and dir /s answers the question but doesn't fix the task.

@OrestTkachuk

This comment has been minimized.

OrestTkachuk commented May 22, 2017

@bryanmacfarlane - OK, thank you for the explanation and the example of using Command Line build step, it will help me in further development!

@sandorfr

This comment has been minimized.

Contributor

sandorfr commented Jan 22, 2018

@bryanmacfarlane the sqlpackage version in the visual studio folder seems to be is a legacy version (as explained here) and with it we can run into azure deployment issues.

My understanding is that the hosted images do not include DacFramework.msi and as such do not contain the up to date standalone sqlpackage.exe. I feel like it should be included, or better we should have a tool installer task which would also put the selected version of sqlpackage in the path.

I was about to give it a go but, there no easy constant point to get the binaries from. The only way to get the current version is through Microsoft download, The other option I would have liked better if this nuget package https://www.nuget.org/packages/Microsoft.Data.Tools.Msbuild/. However it is already lagging one version behind. Sadly https://www.nuget.org/packages/Microsoft.SqlServer.DacFx.x64/ which seems up to date only contains the libraries but not the executable.

If you have any insights on the subject I'd be happy to submit a task around that (or add it to one of our extensions).

@Ajay-MS

This comment has been minimized.

Contributor

Ajay-MS commented Jan 22, 2018

@sandorfr

In hosted agent,
Path of SQLPackage.exe in visual studio folder as follow (Legacy)
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe

Path of SQLPackage.exe with SQL Server ( Latest )
C:\Program Files (x86)\Microsoft SQL Server\140\Dac\bin\SqlPackage.exe

We have logic in task to select latest version of SQLPackage.exe among VS, SQL server and DacFx during the execution. So latest version of SQLPackage.exe should be ideally selected. And you task should have been executed with version 140 instead of 130.

If that's not working for you please share logs, we will look into issue.

Yes I am agree with you that latest version of DacFx should be installed to hosted agent as we gets frequent updates on DacFx. I will check this and will update you on timelines regarding this.

@sandorfr

This comment has been minimized.

Contributor

sandorfr commented Jan 22, 2018

Yes it should definitely be up to date because Azure SQL Team expects it to be up to date:
image

I still think that a tool installer would ease the usage as it would make it easier for people using on premise agent to be up to date and it could put the right version in the path.

Regarding the issue it is not related to your task directly but on the one we are developing (which support multiple dacpac deployment). I guess we are not up to date on the selection of the appropriate location. I know where to look thanks to you :)

@sandorfr

This comment has been minimized.

Contributor

sandorfr commented Jan 23, 2018

@Ajay-MS I tried the path you indicated and it does not exist on hosted 2017 agents (EAU region in the present case). It seems to be: C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe

error log:

2018-01-23T03:58:06.3344855Z ##[debug]Evaluating condition for step: 'Run C:\Program Files (x86)\Microsoft SQL Server\140\Dac\bin\SqlPackage.exe'
2018-01-23T03:58:06.3345790Z ##[debug]Evaluating: succeeded()
2018-01-23T03:58:06.3345964Z ##[debug]Evaluating succeeded:
2018-01-23T03:58:06.3346223Z ##[debug]=> (Boolean) True
2018-01-23T03:58:06.3346509Z ##[debug]Expanded: True
2018-01-23T03:58:06.3346712Z ##[debug]Result: True
2018-01-23T03:58:06.3347220Z ##[section]Starting: Run C:\Program Files (x86)\Microsoft SQL Server\140\Dac\bin\SqlPackage.exe
2018-01-23T03:58:06.3350788Z ==============================================================================
2018-01-23T03:58:06.3350971Z Task         : Command Line
2018-01-23T03:58:06.3351082Z Description  : Run a command line with arguments
2018-01-23T03:58:06.3351189Z Version      : 1.1.3
2018-01-23T03:58:06.3351311Z Author       : Microsoft Corporation
2018-01-23T03:58:06.3351429Z Help         : [More Information](https://go.microsoft.com/fwlink/?LinkID=613735)
2018-01-23T03:58:06.3351597Z ==============================================================================
2018-01-23T03:58:06.3363480Z ##[debug]Working directory: 'd:\a\1\s'
2018-01-23T03:58:06.3363985Z ##[debug]Fail on standard error: 'False'
2018-01-23T03:58:06.3364247Z ##[debug]Modify environment: 'False'
2018-01-23T03:58:06.3364534Z ##[debug]C:\Windows\system32\cmd.exe /c ""C:\Program Files (x86)\Microsoft SQL Server\140\Dac\bin\SqlPackage.exe" /Action:Publish /SourceFile:"d:\a\1\a\Packages\Database\TestDatabase.dacpac" /TargetConnectionString:"Data Source=(localdb)\TestDBServer;Initial Catalog=TestDatabase;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False" /p:CreateNewDatabase=True"
2018-01-23T03:58:06.3364877Z ##[command]"C:\Program Files (x86)\Microsoft SQL Server\140\Dac\bin\SqlPackage.exe" /Action:Publish /SourceFile:"d:\a\1\a\Packages\Database\TestDatabase.dacpac" /TargetConnectionString:"Data Source=(localdb)\TestDBServer;Initial Catalog=TestDatabase;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False" /p:CreateNewDatabase=True
2018-01-23T03:58:06.3547024Z The system cannot find the path specified.
2018-01-23T03:58:06.7691002Z ##[error]Process completed with exit code 1.
2018-01-23T03:58:06.7731697Z ##[debug]System.Exception: Process completed with exit code 1.
   at Microsoft.VisualStudio.Services.Agent.Worker.Handlers.ProcessHandler.<RunAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.VisualStudio.Services.Agent.Worker.TaskRunner.<RunAsync>d__24.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.VisualStudio.Services.Agent.Worker.StepsRunner.<RunStepAsync>d__1.MoveNext()
2018-01-23T03:58:06.7733884Z ##[section]Finishing: Run C:\Program Files (x86)\Microsoft SQL Server\140\Dac\bin\SqlPackage.exe
@Ajay-MS

This comment has been minimized.

Contributor

Ajay-MS commented Jan 23, 2018

Hello sandorfr

Available paths for SQLPackage.exe in Hosted 2017 Agent

  • C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe
  • C:\Program Files\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe

Available paths for SQLPackage.exe in Hosted Agent

  • C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe
  • C:\Program Files\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe
@soccerjoshj07

This comment has been minimized.

soccerjoshj07 commented Mar 16, 2018

Is there any updates on this? I have SSDT 2017 installed on a build server as a private agent. SQLPackage.exe is not added as a capability even though SSDT 2017 is installed. I tried manually adding the sqlpackage.exe location to the PATH system environment variables and that did not help either. Installing SSDT 2015 adds the SQLPackage.exe as a capability, but not SSDT 2017. The above proposed solution does not help as you need the sqlpackage capability for an Azure Database deploy task. You could of course add the sqlpackage capability manually and point to the above location, but that seems like something you shouldn't have to do.

@bojingo

This comment has been minimized.

bojingo commented Sep 28, 2018

I have private build agents that for some reason just got the "150" DAC tools installed on half of them but not the other half. These agents all have multiple versions of the DAC tools, but the vsts task seems to just pick the newest, even though for the agents with the newest it doesn't work.

Please, please, please add an option to the vsts task to explicitly specify which version of the DAC tools to use. If I have 130, 140, and 150 in the Visual Studio install path, and my DACPAC only deploys successfully with 140, let me specify that!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment