CODEWITHSUNDEEP
sql-serversql-server-data-tools
randmatt
randmatt

Reputation: 159

SSDT - Database Reference

I have a database project in Visual Studio 2012 that requires access to a database not of my design through a linked server.

My initial approach was to script out the access to the database in 4-parts [server].[database].[schema].[table]. This approach throws SQL71562a and SQL71501 warnings/errors.

Through research I note that what I need to do is to create a database reference. My question would revolve around that process.

I thought the right thing to do would be to generate a DACPAC from the other vendor's database as it exists on the server - but I am unable to generate the DACPAC as it fails in SSMS (I think due to encryption).

My next idea would be to create a project that has the schema that I need from that database defined and then reference that (I haven't tried this yet as at first guess it is probably the wrong way).

Any help on this would be appreciated. Thanks in advance.

Upvotes: 1

Views: 1122

Answers (1)

Jorge Candeias
Jorge Candeias

Reputation: 720

SSMS is very pedantic when it comes to extract DACPAC files. You can however, use the SqlPackage utility for that:

SqlPackage /Action:Extract /SourceServerName:YourServerNameHere /SourceDatabaseName:YourDatabaseNameHere /TargetFile:YourDatabaseNameHere.dacpac

I had an issue just like that, in which this solved it, and documented it in:

http://tangodude.wordpress.com/2014/02/05/referencing-the-sql-server-data-collectors-management-data-warehouse-in-your-ssdt-database-project/

Creating a dummy/stub project does work though, but using a DACPAC file reference is much much simpler.

Upvotes: 1

Related Questions