DB Deployments: Using A Custom Filter

So I’ve been rolling out the deployment pipelines as I was able and I ran into an issue with our third one: We have a helper database that lives on all of SQL Instances and while we’d like to eventually keep any customization in source control we only want the dbo schema deployed automatically via the pipeline. The Redgate “Deploy From Package” step has a handy option to include an alternative filter to use with the SQL Compare engine so it seemed really straight forward: I fired up SQL Compare: generated a unique filter file that I named DeployFilter.scpf and threw that file name into the job step!

deployfilter

Unfortunately I got stuck here because this does not work. The nuget packages don’t end up in the working directory for the deployment and I couldn’t find any documentation about where it might end up. I ran a few deployments so I could watch the working folder as the build ran to see what showed up with no luck. Looked for articles etc. that might have mentioned configuring the path (to no avail). Eventually I hit up the Redgate channel on the SQL Slack. Alex Yates (b|t) had the answer in his head all along: The previous step (self-deploy package) unpacks the nuget package on the tentacle and I have to get it to tell me where the package is (and then append the path to the filter within the package itself). This is all very long way of saying if you want to use a custom filter (a) it should be in source control with the rest of your code and (b) the variable is #{Octopus.Action[<name of package deployment step>].Output.Package.InstallationDirectoryPath}\<path to filter file in nuget package>. And I am putting it here for the next person that finds themselves googling how to make it work (which may still be me: my buffer is notoriously overworked).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.