How to add a custom SQLCLR assembly to a Visual Studio Database Project

As it turns out this is very simple. In the Database project add a reference to the SqlClr project. The database project will then automatically identify the assembly, and add it to it's set of scripts.

With the assembly added to to the database, it's a simple matter to reference it in the create scripts of the CLR types, functions, aggregates, etc.

Step by step to adda SQLCLR aggregate function: 1) right-click the database project -> Add Reference. Select the SQL CLR project from the list of projects in the solution

2) Switch to the database Schema (View -> Database Schema View) Verify that the Assembly appears in the Assemblies foler

3) Drill down to the Functions Folder (Schema -> dbo -> Programmability -> Functions) Right click and Add -> Function -> Aggregate Function

Edit the template accordingly to create the aggregate.

Now the database project is the a complete unit of deployment and the SQL CLR project is not needed as part of the deployment.

This two project file are msbuild project files. Therefore you can use a msbuild script to drive both of them.

Database Project imports this file. $(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets

In my 64 bit machine

C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData

It has targets like DspBuild DspSetupDeploy DspDeploy

While SqlClr project imports

C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets

It has targets like

SqlClrDeploy

A simple msbuild file which calls both of deploy target should be enough.

Or you can customize database project's file to call before build to other file's target. Which will be more complex.