Azure DWH (Paas) Cross Database Queries Possible?

SSIS and Import/Export
1

Setting up DWH on Azure combining Azure SQL VM (SSIS) with Azure SQL DWH (backend)

Deployed the ETL packages to Integration Services using ISPAC and DWH from on-prem SSMS using 'Deploy to Windows Azure SQL Database' function.

DWH Architecture has build model where staging tables, DIMS, FACTS are stored in 'different' database meaning when the ETL load cycle is performed a cross database query is required to perform the MERGE operations. And Azure doesn't permit cross database queries/or does it? Mixed information out there around LinkedServers, OpenQuery, Azure versions.

Anyone else encountered this as yet?

Msg 40515, Level 15, State 1, Line 30
Reference to database and/or server name in 'mydb-dwh-azure@database.windows.net.MyDB.dbo.DimTbl' is not supported in this version of SQL Server.

Thanks

2

Hi you should go throgh this one.

3

Hi,

Have previously seen James's excellent blogs on Azure DWH. However don't remember the topic of cross database queries being included (did check again)

This MS article will say not a support feature as yet https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/

And this article https://azure.microsoft.com/en-us/blog/announcing-updates-to-windows-azure-sql-database/ says Linked Servers and Distributed Queries are support however maybe attributed to the SQL Azure DB and not the SQL Azure DWH,

Will be testing shortly and shall update this post. Thanks for your input so far - appreciated.