Where to start with SQL VS Project

Integration
1

Hi,

So I’m hoping for a bit of nudge in the right direction.
The goal is to deploy a SQL Server database which is in a visual Studio project in SVN. Currently we use Team City to do the build and deployment. Essentially its 3 steps that I need to do;

  1. Get the project from SVN.
  2. Build the DACPAC
  3. Deploy it.

Its points 1 and 2 that I’m struggling with, if I go add a step to my project I can see there a DAC PAC deployer, so that takes care of point 3, but I’m after a bit of guidance on how I would achieve points 1 and 2. Is there anything pre-built that I could use or would I have to run a powershell script to do this?

Any help at all would be appreciated.

Thanks,

Nic

2

Update, so it turns out that I have the Team City bit work so it produces the DACPAC from SVN but now I need to get it to pass the DACPAC to Octopus so that it can deploy it.

Any ideas on how I would do this?

Thanks,

Nic

4

Hi @NicHopper!

I have a few ideas, yes :wink:

Easiest way would be to install our TeamCity plugin, and then you can just add steps in your build process in TeamCity to push your package to Octopus, and optionally, create and deploy the release.

We’ve got some handy resources for setting all of this up. Here’s a few helpful links:

I hope these links get you up and running, and really optimise your build and deploy pipeline moving forward!

Please don’t hesitate to let us know if you have any further questions.

5

Hi,

Thanks for the reply. Progress has been good but we’ve now hit an error that’s got me confused. So as of now the process is;

  1. Team City does the build, produces a DACPAC.
  2. Team City then converts this to a nugget package using octo pac.
  3. It then passes the package to Octopus.
  4. It then creates a release for the project in Octopus.

This all works as expected but the project in Octopus has 2 steps;

  1. Is to prep the package feed and this works fine.
  2. It then use the pre-built SQL - Deploy DAC PAC using step 1 as the feed and its here where I get the error message;

Could not find the file
August 9th 2019 09:39:14Error
‘D:\Octopus\Applications\Dev\PipeLineSandbox\1.0.15\DEV’
August 9th 2019 09:39:14Error
At D:\Octopus\Work\20190809083912-41470-19\Script.ps1:450 char:9
August 9th 2019 09:39:14Error

  •     Throw ("Could not find the file '{0}'" -f $PublishProfile)

August 9th 2019 09:39:14Error

  •     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

August 9th 2019 09:39:14Error
+ CategoryInfo : OperationStopped: (Could not find …box\1.0.15\
August 9th 2019 09:39:14Error
DEV’:String) , RuntimeException
August 9th 2019 09:39:14Error
+ FullyQualifiedErrorId : Could not find the file ‘D:\Octopus\Applications
August 9th 2019 09:39:14Error
\Dev\PipeLineSandbox\1.0.15\DEV’
August 9th 2019 09:39:14Error

August 9th 2019 09:39:14Fatal
The remote script failed with exit code 1
August 9th 2019 09:39:14Fatal
The action SQL - Deploy DACPAC on failed

Originally I thought that it was trying to find my DEV.Publish.xml which is in the project but having looked around it seems I may be missing a step where I create a publish file. This is based purely on this article;

Unfortunately the link that may have the answer on it now just directs me to a generic Octopus page.

Any ideas on what I’m missing and how I can resolve it?

Thanks,

Nic

6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.