Monday, August 20, 2012

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.

5 comments:

  1. Top post! This saved me the long road to get the DAC framework officially scripted and distributed to the DBA's machines to get my DACPACS deployed

    ReplyDelete
  2. good it is documented 1 year before, but i came through the pain recently and fixed it with SSDT project.

    Here you go for more information about SSDT:
    http://www.zerothoughts.in/index.php/2013/09/03/database-automation-using-ssdt/

    Boilerplate structure for development:

    https://github.com/GomesNayagam/SSDT-Boilerplate

    ReplyDelete
  3. Is there a way to get OutputPath to create a directory that doesn't exist? I'm doing this through a powershell script and want to output the report and script to a directory based on a build number variable passed to the script.

    ReplyDelete
  4. This is great is there a line for creating the dacpac and do you have information on what Target connections are available?

    ReplyDelete
  5. This is a great post we are looking at implementing the same but using a package.xml file for the connection string.

    ReplyDelete