Deploy Azure SQL Elastic Database query

Question

I have multiple Azure SQL databases. One database holds all the staging table and the other database holds all the fact/dim tables. Now in my development environment I have stored procedure which reads data from staging tables (from staging database) and loads the data into its respective fact/dim tables (in a different database).

The above scenario is all working fine. I have multiple SQL projects for each database.

Now how do I deploy the elastic database queries while deploying the dacpac?

Below is the error when I add the elastic queries as a part of my post deployment scripts in Visual studio and try to build it.

PS: The SQL project properties is set to target the V12 version of the Azure SQL Database

Answer 1

Which version of the SQL Server Data Tools (SSDT) are you using? If you are not using the latest (14.0.60413.0), give it a try to upgrade SSDT: https://msdn.microsoft.com/en-US/mt429383. With that version, I am now successfully able to compile and publish database projects and dacpacs.

Answer 2

To successfully build, you will need to install the latest SSDT Preview which includes support for these objects from here. Support for the latest Azure SQL DB and SQL Server 2016 features is only available in the Preview for VS2013 at present, whereas for VS2015 the support was shipped in VS2015 Update 2. Once SQL Server 2016 goes GA (June 1, 2016) an RTM update will be pushed through the Visual Studio 2013 Extensions and Updates channel containing this support. That will ensure you get monthly updates with the latest changes again.

Note that even with the latest bits, if you open the file itself you'll get an issue with the Intellisense parser. Building the project will work fine but on opening a document you will see the errors appear for that specific document. Note that the same issue occurs in SSMS when coding this into a query window. This is because the Intellisense parser is separate from the core build system. The fix for this is in progress and will land in a post-SQL Server 2016 update (likely late June - July timeframe).

Disclosure: I work on the SQL Server tools team.