SqlPackage.exe - Automating SSDT Deployment

To know about SQL Server Data Tools (SSDT), take a look at http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx

Below are the steps for automating the SSDT deployment:

• Create a Folder on the deployment machine. We can call it C:\SSDT
• Make sure the files from the following folders on the development machine are copied in SSDT folder on the deployment machine:
• C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin
• C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.TransactSql.ScriptDom\v4.0_11.0.0.0__89845dcd8080cc91
• Copy the files from the Build Output of the SSDT Project in a Folder on the deployment machine. We can call it C:\Build
• Make sure that you have the connection string of the Target Database where you want to depoy. We can call it connStr
• Now you can publish or generate the scripts for the database deployment (Steps are same for Full or Incremental Deployment)
• To Publish, run the command: C:\SSDT\SqlPackage.exe /Action:Publish /SourceFile:"C:\Build\MyDatabase.dacpac" /TargetConnectionString:"connStr"
• To Generate Scripts, run the command: C:\SSDT\SqlPackage.exe /Action:Script /SourceFile:"C:\Build\MyDatabase.dacpac" /TargetConnectionString:"connStr" /OutPutPath:"C:\Build\MyDatabase.sql"

This is great feature for SQL Azure databases since incremental updates can be applied very easily without making the sql scripts SQL Azure compatible.