Deploying a SQL DACPAC and Populating Database via PowerShell (Part 1)

We had a situation recently where we needed to deploy a SQL 2012 database packaged as a SQL data tier application to an existing server. However, we needed to do so on demand, not as part of a VMM service template. The quick answer was to leverage a PowerShell function to load the DAC libraries and deploy the database.

The sample below will perform the following steps:

  • PowerShell Function to Deploy SQL data tier application (DACPAC)
  • Calling the Function
  • Populating the Database (T-SQL script and PowerShell)

PowerShell Function to Deploy SQL DACPAC

This sample is intended to run locally on the SQL 2012 Server, but can call a remote DACPAC and T-SQL script located, for example, in the VMM library.

With the function in place, you are now ready to deploy the SQL DACPAC.

Call the Function

Call the function and provide 1) ServerName, 2) full path to DACPAC file and 3) and name of the DB being deployed. This sample deploys the StockTrader sample database.

Populating the Database

If DB install succeeds, run the SQL Script (containing table data) to populate the database. This sample populates the StockTrader database with a sample SQL script containing table data.

With the -verbose flag on, the output is echoed to the screen so you can watch progress.SQL_Script

In a follow-up post, we’ll take this sample and move it into Orchestrator for deployment on demand as part of larger automation sequences. We’ll also look at PowerShell DSC Wave 3 released only a couple of days ago, which provides DACPAC deployment samples.

2 thoughts on “Deploying a SQL DACPAC and Populating Database via PowerShell (Part 1)

  1. Profile photo of sheirsheir

    I am in this boat as I have manage to create a Database Project in VS2013 that deploys a database to a SQL Server 2014 server and populates a series of ListOfValues tables.

    Now I want to populate that database with data that has to come from another database whose schema is not the same.  Hence I have to do some massaging of the data.

    How best to that?  Can I do that in another Database Project and then have that Project run after the first one succeeded?

     

     

  2. Profile photo of raju Kraju K

    param(
    [string]$sqlserver = ‘XXXXX’,
    [string]$dacpac = ‘C:\sqlinstall\DBA_MAINT\DBA_MAINT.dacpac’,
    [string]$dbname = ‘DBA_MAINT’)
    #[string]$sqladminuser = ‘sa’,
    #[string]$SqlAdminPassword = ‘SRcab#_Egpnt67’ )

    Write-Host “Deploying the DB with the following settings”
    Write-Host “sqlserver:$sqlserver”
    Write-Host “dacpac: $dacpac”
    Write-Host “dbname: $dbname”

    # load in DAC DLL (requires config file to support .NET 4.0)
    # change file location for a 32-bit OS
    add-type -path “C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\Microsoft.SqlServer.Dac.dll”

    # make DacServices object, needs a connection string
    $d = new-object Microsoft.SqlServer.Dac.DacServices “server=$sqlserver”

    # register events, if you want ’em
    register-objectevent -in $d -eventname Message -source “msg” -action { out-host -in $Event.SourceArgs[1].Message.Message } | Out-Null

    # Load dacpac from file & deploy to database named pubsnew
    $dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)
    $d.Deploy($dp, $dbname, $false) # the true is to allow an upgrade, could be parameterised, also can add further deploy params

    # clean up event
    unregister-event -source “msg”

     

    Please help me on this issue.

    Using above script i got below error:

    Deploying DBA_MAINT Database
    Deploying dacpac
    Exception calling “Deploy” with “3” argument(s): “Could not deploy package.”
    At C:\Users\nagarajk$\Downloads\sqlinstall\SqlPostInstall.ps1:85 char:5
    + $dacService.Deploy($dp, $dbname, “True”);
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DacServicesException

     

    Exception calling “Deploy” with “3” argument(s): “Unable to determine the
    existence of database ‘DBA_MAINT’. You must have a user with the same password
    in master or database ‘DBA_MAINT’.”
    At line:25 char:1
    + $d.Deploy($dp, $dbname, $false) # the true is to allow an upgrade, co …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DacServicesException

Leave a Reply