Reverse Engineer a MDS database as a .dacpac generates errors

Question

I am trying to extract a Data-tier application from my Master Data Services database. I need this for SQL Server Data Tools Db project that needs to reference the MDS database. But when following the wizard steps in SSMS to extract to a dacpac, I get errors. And the errors are stuff that are in no relation to the stuff I have built in the database. For example, the first error is this:

Error SQL 71562: Procedure: [mdm].[udpDBErrorSave] has an unresolved reference to object [master].[sys].[syslanguages].

My login is a sysadmin on the server, so why can this wizard NOT find things in the master db? I have tried loading the database into a SQL Server Data Tools Database Project, but that has issues as well.

Any ideas?

---

Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

Answer 1

This is a question for the SQL Server Data Tools forum.  As far as MDS is concerned, it's certainly not supported to _deploy_ the MDS database from a .dacpac, but the real questions are:

1) Whether it's necessary to reverse engineer it into a .dacpac to manage your dependent database in a SQL Server Data Tools Db project

2) Whether it's possible to do so.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

I don't know what dacpac is, but regardless of that, if you trying to manipulate the MDS tables you're probably going the wrong way in your solution design.

---

Half Scheidl

Answer 3

A "dacpac" file is for "Date Tier Application". I have no intention of manipulating the MDS schema from within the SSDT solution/project. BUT, I have OTHER databases that either read from the Master Data subscription views, or load data into the staging tables for manipulating entity attributes. Those other databases need to have a reference to the MDS database in order for their respective Build operation to complete without errors.

To get around this, I have basically placed black electrical tape over the "Check engine" light on the dashboard. It makes it so I don't see the errors and warnings, but also does not guarantee that it will work once I deploy the database, as it is ignoring the warnings and errors.

Oh, well...

---

Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

Answer 4

Hi Todd,

  I was just working on a similar problem as I needed to "augment" MasterData DB for my ETL purposes (creating a view on the mdm.tblCodeGen table resolving to the name entities as I have some logic to generate codes that I want to work in concert with what MDS does). I had trouble extracting dacpac using the SSMS wizard, but I was successful using the sqlpackage.exe tool. This is what I did:

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin>sqlpackage.exe /a:Extract /ssn:localhost /sdn:MasterData /tf:"c:\MasterData.dacpac"
Connecting to database 'MasterData' on server 'localhost'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file 'c:\MasterData.dacpac'.

This response is way too late, but may be it will help someone...

Alex

Answer 5

Ah, yes. SQL 2012 functionality only. Fortunately we are now on that platform.

I was able to resolve my original issue with a bit of brute-force: I created a blank database project in the solution that other DB Projects could reference. Then I manually added objects for those items that were in the dependence chain, things like the staging tables and some subscription views. But instead of creating the subscription view as a VIEW, I created it as a table in the 'Master Data Services' DB project. Now, other projects can reference that 'object' and not care if it is a table or a view. All the referencing code needs is the same metadata that would be returned by the view.

With the move to SQL 2012 MDS, there are more objects tables to worry about as every entity has a staging table AND a stored procedure associated with it, instead of the more generic ones from 2008 R2.

I will try your approach and see how it goes.

---

Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

Answer 6

Hi,

  I am not sure if this is 2012 functionality per-se, although I am running it against 2012 instance. Even from SQL 2012 SSMS I was getting an error about resolving references to MSDB during the DACPAC extract of the MasterData DB.

SqlPackage.exe itself is part of DACFx I believe - so it might be just a matter of updating to a later version of DACFx even if you will run it against a 2008 instance. The latest versions of SSDT and DACFx are available from this link: http://msdn.microsoft.com/en-US/data/hh297027

In my case I am actually extracting a sort of "pristine" dacpac of the MDS database and using it as a project reference in my MDS SSDT project to add my custom objects into the database (views) and run post-deployment scripts (adding extra datetime mask) on different dev/prod instances. I am deploying into the MDS database itself.

If you are relying on referencing the export views from other DBs, perhaps the dacpac is not the best solution for you as it will probably be more cumbersome to continuously regenerate the dacpac and update the project reference every time an export view is added or modified in the MDS. Your current approach is probably more convenient as you can run the comparison and sync changes into your local project. I think you can improve on it by creating a blank SSDT project, then using the Import Database option to create the schema of the MDS objects instead of having to create tables for equivalent MDS views. Subsequently you can use the "Schema Compare" function to sync changes from live DB into your project.

Alex