Redistribution of sqlpackage.exe [SSDT]

jamiet — Wed, 30 May 2012 14:15:00 GMT

This is a short note for anyone that may be interested in redistributing sqlpackage.exe. If this isn’t you then no need to keep reading. Ostensibly this is here for anyone that bingles for this information.

sqlpackage.exe is a command-line that ships with SQL Server Development Tools (SSDT) in SQL Server 2012 and its main purpose (amongst other things) is to deploy .dacpac files from the command-line. Its quite conceivable that one might want to install only sqlpackage.exe rather than the full SSDT suite (for example on a production server) and I myself have recently had that need. I enquired to the SSDT product team about the possibility of doing this. I said:

Back in VS DB Proj days it was possible to use VSDBCMD.exe on a machine that did not have the full VS shell install by shipping lots of pre-requisites along for the ride (details at How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE).

Is there a similar mechanism for using VSDBMCD.exe’s replacement, sqlpackage.exe?

here was the reply from Barclay Hill who heads up the development team:

Yes, SQLPackage.exe is the analogy of VSDBCMD.exe. You can acquire separately, in a stand-alone package, by installing DACFX.

You can get it from:

Feature pack is here: http://www.microsoft.com/en-us/download/details.aspx?id=29065

Web Platform Installer here: http://www.microsoft.com/web/gallery/install.aspx?appid=DACFX

You will notice it has dependencies on SQLDOM and SQLCLRTYPES. WebPI will install these for you, but it is al carte on the feature pack.

So, now you know. I didn’t enquire about licensing of DACFX but given SSDT is free I am going to assume that the same applies to DACFX too.

@Jamiet

UPDATE: Gert Drapers has posted an article Headless MSBuild Support for SSDT (*.sqlproj) Projects in which he describes five seperate installations that need to be performed in order to run SSDT deployment without installing Visual Studio:

dacframework.msi
SQLDOM.msi
SQLLS.msi
SQLSysClrTypes.msi
SSDTBuildUtilities.msi

(see Gert's article for links to those five installation files)

Notice that two of those are mentioned in Barclay's quote above. I do not know if the other three are installed by WebPI or even whether they are required for sqlpackage.exe to work, one day I hope to get a clear answer on that from someone at Microsoft. Until that data comes, use the information here and in Gert's article to get your deployments working.

SQL Server Data Tools does support required variables

jamiet — Sun, 12 Feb 2012 11:58:24 GMT

Over the past few years of using datadude (aka DBPro aka Visual Studio Database Projects) I have fallen prey to a peculiar little nuance – if you forget to supply a value for a sqlcmd variable then it will simply use a default and often that is not the desired behaviour. Hence why yesterday I submitted the following suggestion to http://connect.microsoft.com/sqlserver/feedback :

Specify sqlcmdvars properties as "required to be overridden”

In my current place of work I am responsible for maintaining our datadude projects and we have another team that is in charge of deployments. Hence, when we place new properties into the sqlcmdvars file I need to tell the deployment team what values to supply for that property per environment (dev, systest, uat, prod).
Unfortunately lack of communication/human error occasionally creeps in and, for whatever reason, no value gets supplied for some property at deployment time. If this is the case the default value as specified in the sqlcmdvars file gets used instead - invariably this will be the wrong value. I would like a mechanism within SSDT of preventing this from ever happening.

One simple way to prevent this would be to specify that a sqlcmdvars property is *required* to be overridden during the deployment. In other words, never use the default. if an override is not supplied at deployment time then the deployment should fail.
Note that this stipulation should only be in place when deployment occurs from the command-line - if deploying from Visual Studio the default should be allowed (simply because Visual Studio doesn't provide a way to specify anything other than the default value supplied in the sqlcmdvars file).

https://connect.microsoft.com/sqlserver/feedback/details/724366/ssdt-specify-sqlcmdvars-properties-as-required-to-be-overridden#details

It transpires that this requested feature is already available in the forthcoming SQL Server Data Tools (SSDT) as I shall now demonstrate. This screenshot shows the project properties of a SSDT project where we define the sqlcmd variables, I have defined a variable called $(Id_value):

In SSDT the nomenclature for deploying a project is “Publish”, a function that can be found by right-clicking on a project in Solution Explorer:

Selecting that brings up the Publish dialog.

Notice how the “Publish” button is greyed out – that it because I have not supplied a value for $(Id_value); supplying a value enables the Publish button and I can go ahead and publish my project. In other words, SSDT insists that I supply a value for that variable – exactly as I requested in my Connect submission.

The same is true if I use the command-line publishing tool sqlpackage.exe. The following command does essentially the same thing as the above depicted Publish dialog:

>sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac"

Executing that gives me an error:

Publishing to database 'MyDB' on server '.\RC0'.
*** Missing values for the following SqlCmd variables:
'Id_value'.

Here’s a screenshot showing the same:

In order to supply a value for the variable from the command-line you need to use the /v: switch like so:

>sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac" /v:Id_value=1

As you can see, publish was successful!

So there you go, using SSDT you’ll no longer be able to fall prey to the problem I highlighted at the top of this blog post. In a later blog post I’ll show how you CAN supply a default value if you want to and also how you can override for your local environment.

@jamiet

Search results matching tags 'SQL Server Data Tools' and 'sqlpackage.exe'

Redistribution of sqlpackage.exe [SSDT]

SQL Server Data Tools does support required variables

Specify sqlcmdvars properties as "required to be overridden”