1

I'm just getting my feet wet with SSDT because I'm excited about its source control and deployment capabilities. I'm working on an instance of Microsoft Dynamics GP which consists of a couple thousand tables, about 20k stored procs, etc. I won't be modifying the schema very much, instead only working to modify a handful of the procs (probably a couple hundred max).

However, creating a new SSDT project off of our live instance to facilitate this work creates a project that won't build, and the number of errors is fairly massive. Suffice to say correcting 21570 errors won't be feasible for the scope of this project given the relative size of the work we actually mean to do--especially since we didn't author it ourselves to begin with.

I don't want to give up hope, though--I'm curious if it's possible just to use SSDT to house & maintain the procs we're using, and deploy only the scripts we modify even though there's much more to the databases that we don't touch. Is this at all realistic?

bwerks
  • 8,651
  • 14
  • 68
  • 100

1 Answers1

2

Try creating a dacpac of the existing database (use SQLPackage to generate it), then reference that as a DB Reference in your project as "same database". I blogged about using SQLPackage and database references here: http://schottsql.blogspot.com/2012/10/ssdt-external-database-references.html

That way your project will contain just the objects you are creating but still reference the existing objects.

Peter Schott
  • 4,521
  • 21
  • 30
  • This just blew my mind. Not only does it address the separation of purchased vs customized objects, but it also does away with building databases that are referenced and not modified with our work, which is only just now beginning to become a pain for us. I'm going to pursue this vigorously and I hope to credit this as the answer. – bwerks Jul 22 '13 at 20:42
  • I've tried this approach, and I think I've uncovered a bug of some kind in SSDT. Instead of using the Create New Project workflow with my database, I extracted a dacpac instead. However, when attempting to add a database reference to my dacpac in my from-scratch SSDT project, the Add　Database Reference dialog simply disappears immediately after I select the dacpac file. Editing the reference in manually in MSBuild prevents the project from ever loading. The dacpac is just under 10MB for what it's worth. – bwerks Jul 23 '13 at 08:42
  • Did you try extracting using SQLPackage and not SSDT? I've had some odd issues extracting from within SSDT though it should work. Of course, I don't have a 10MB dacpac file, either. Most of mine are significantly smaller. – Peter Schott Jul 23 '13 at 21:15
  • Yeah, I tried both SSDT's "Extract data-tier application" workflow as well as sqlpackage.exe at the command line. I think it might just be too big; unpacking the 10MB dacpac results in a 300MB xml file that weighs in at a little over 8 million lines. Quite a large database. My next approach is going to be attempting to identify the objects upon which I depend, and extracting only those ones using the TableData parameter for sqlpackage. Here's hoping. – bwerks Jul 24 '13 at 02:37
  • Incidentally I've read through most of your blog on SSDT stuff, and it's been exceedingly helpful in getting acquainted with SSDT. – bwerks Jul 24 '13 at 03:07
  • Yeah, extracting just the objects you need would be the next way I'd go. You may be able to make a SQLProject out of it, then find the largest objects by looking at files sizes and purging them. :) As for the SSDT stuff, I owe a bunch to Jamie Thomson. He did a bunch of work on DB Projects and I learned from him. Highly recommend you look up his stuff as well. – Peter Schott Jul 24 '13 at 22:07