Hello!

I’ve been working with a bunch of database projects that have a huge amount of interdependency amongst them, and pretty awkward it is to. In addition to this awkwardness is SQLCLR, which is not something I’ve encountered before in a SQL Database. There was something similar in a SSAS project I worked on, but that was long ago. At any rate, automating the deployments just got a little more complicated thanks to this dll which is transformed into a string of seemingly random bits in a SSDT Publish script. And so because my PowerShell module had never had to build or release anything that used SQLCLR I created a project based on this sample and compiled/released it via the magic of PoshSSDTBuildDeploy. Happily PoshSSDTBuildDeploy does cope with SQLCLR… or rather, SSDT copes with it and my wrapper works.

However, one thing I did notice was that each time I published the changes, the dll was published. Now in the grand scheme of things this is not something to get stressed about, but because I subscribe to the notion that the only things to be changed are indeed those things that have been changed (so that there’s less risk of things going wrong when running a release), I wanted to find out why.

Come to find out that because the cs file is within the sqlproj, when it gets built on my build server, the dll is considered to be altered, and so it need re-deploying. This is in no way any criticism of the blog post referenced above, because it’s a great example of getting started with SSDT and SQLCLR. And if you don’t want to have to re-deploy your dll even if no change has been made, the solution is to create a library project and then add a reference to it from the database project. If you are running this on a build server then do not clean the directory. This may still cause an issue if you are using a wide pool of build agents and the cs project has changed from the previous “get” of source control. But I honestly don’t think this will be too much of an issue.

To illustrate what I mean, I have taken the sample project form Simon’s blog above, and modified it. You can find it on GitHub along with the rest of my mausoleum of code.