0

I've got a question about building a deployment script using SSDT. Could anyone tell me if it's possible to build a deployment script using SQLPackage.exe where the source file is NOT a dacpac file, but uses the .sql files instead?

To give some background, I've created a project in Visual Studio 2012 for my database schema. This works great, and SSDT builds the folder structure without a problem (functions, stored procedures etc which contain all the .sql files).

Here's the problem - the database in question is from a legacy system, and is riddled with errors. Most of these errors we don't care about anymore and it's not practical or safe to fix them all, so for years we've basically ignored them. However it means we can't build the project and therefore can't generate the dacpac file. Now this doesn't prevent us from doing the schema compare and syncing the database with the file system (a local mercurial repository). However it does seemingly prevent us from building a deployment script.

What I'm looking for is a way of building the deployment script using SQLPackage.exe without having to generate the dacpac file. I need to use the .sql files in the file system instead. Visual Studio will produce a script of the differences without building the dacpac, so this makes me think it must be possible to do it using SQLPackage.exe using one of the parameters.

Here's an example of SQLPackage.exe which I'd like to adapt to use the .sql files instead of the dacpac:

sqlpackage.exe /Action:Script   /SourceFile:"E:\SourceControl\Project\Database
\test_SSDTProject\bin\Debug\test_SSDTProject.dacpac" /TargetConnectionString:"Data 
Source=local;Initial Catalog=TestDB;User ID=abc;Password=abc"  /OutputPath:"C:
\temp\hbupdate.sql" /OverwriteFiles:true /p:IgnoreExtendedProperties=True     
/p:IgnorePermissions=True /p:IgnoreRoleMembership=True /p:DropObjectsNotInSource=True

This works fine because it uses the dacpac file. However I need to point it at the folder structure where the .sql files are instead.

Any help would be great.

FrugalShaun
  • 166
  • 7
  • I don't think that's going to be possible in your case. You could choose to not run as a transaction and to try to keep going on error, but your best overall solution is to import from the DB (or a dacpac) and fix the issues or mark those problem objects as "not in build". It will take some time, but the benefits of a good build and clean solution will pay off in the long run. – Peter Schott Apr 11 '14 at 00:32
  • Thanks Peter. Currently SSDT is showing about 60 errors and about 200 warnings, and that's just for one code base (we have about 20!). So you can imagine the scale of the task. I think we can trim it down by tweaking the options. Certainly setting a few as 'not in build' is an option, although I think we may end up biting the bullet and just fixing them as you suggest. – FrugalShaun Apr 14 '14 at 20:24
  • You'd be amazed at the number of warnings that can be eliminated pretty quickly. Removing the current db name (mydb.dbo. -> dbo.) helps quite a bit and adding DB References often go a long way towards fixing those. I do know the scale - we went through it for ~ 13 DBs, most having cross-dependencies. It's daunting, but doable. – Peter Schott Apr 15 '14 at 15:49

2 Answers2

1

As has been suggested in comments, I think that biting the bullet and fixing the errors is the way ahead. You say

it's not practical or safe to fix them all,

but I think you should give this a bit more thought. I have recently been in a similar situation to you, and the key to emerging from it is to realise that the operational risk associated with dropping procedures and functions that will throw an exception as soon as they are called is zero.

Note that this does not apply if the reason these objects won't build is that they contain cross-database or cross-server references that are present in production but not in your project; this is a separate problem altogether, but also a solvable one.

Nor am I in favour of "exclude from build" as an alternative to "delete"; a while ago I saw a project where this technique had been deployed extensively; it makes it harder to see what does what from the source files and I am now of the opinion that "Build Action=None" is simply "commenting out the bits that don't work" for the Snapchat generation.

The key to all of this, of course, is source control. This addresses the residual risk that one day you might indeed want to implement a working version of one of your currently non-working procedures, using the non-working code as a starting point. It also obviates the need to keep stuff hanging around in the solution using Build Action=None, as one can simply summon an earlier revision of the code that contained the offending objects.

If my experience is any guide, 60 build errors is nothing; these could easily be caused by references to three or four objects that no longer exists, and can be consigned to the dustbin of source control with some enthusiastic use of the "Delete" key.

Gavin Campbell
  • 796
  • 5
  • 19
0

Do you have a copy of SQL Compare at your disposal? If not, it might be worth downloading the trial to see if it will work in your scenario.

Here are the available switches: http://documentation.red-gate.com/display/SC10/Switches+used+in+the+command+line

At the very least you'll need to specify the following: /scripts1: /server2: /database2: /ScriptFile:

David Atkinson
  • 5,759
  • 2
  • 28
  • 35
  • Thanks David. SQL Compare is definitely an option. Although since it comes at a significant cost it's more likely we'll end up fixing all the build errors instead. – FrugalShaun Apr 14 '14 at 20:26
  • 1
    One way of identifying build errors is to install SQL Prompt and use the Find Invalid Objects feature. Like all Red Gate tools, SQL Prompt has a full functional 14-day trial. – David Atkinson Apr 14 '14 at 20:58
  • "significant cost" - what about the cost of your time that would be better spent doing something else? Unfortunately, it's a calculation that many bosses don't understand... – Thomas Rushton Mar 06 '17 at 08:59
  • @DavidAtkinson I think they've already found the invalid objects; these are why they are having trouble building the project! – Gavin Campbell Mar 06 '17 at 16:39
  • @GavinCampbell - So do dacpacs need to be entirely invalid-object-free? – David Atkinson Mar 06 '17 at 16:41