At my DAC talk at TechEd last week, the thing that seemed to cause the most interest was the PowerShell scripts to use DACPACs/BACPACs. I thought I'd post that here, along a question about cmdlets in general. Using PowerShell against the library allows admins to use all of the functionality that's in SSMS, with the options available in SqlPackage (think DacOptions object) and more (like BACPACs)… The premise is that administrators (DBAs or others) might want to integrate this functionality as part of their deployment into a PowerShell script.

PowerShell doesn't officially support .NET 4.0 assemblies until PowerShell 3.0, so you'll need a config file. You'd name this PowerShell.exe.config and place it in the same directory as the PowerShell.exe:

<?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0.30319"/>
    <supportedRuntime version="v2.0.50727"/>
  </startup>
</configuration>

Then the scripts. Granted, I'm using the defaults, not the DacOptions or list of tables to export.

# 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\110\DAC\bin\Microsoft.SqlServer.Dac.dll"

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

# register events, if you want 'em
register-objectevent -in $d -eventname Message -source "msg" -action { out-host -in $Event.SourceArgs[1].Message.Message }

# Extract DACPAC from database
# Extract pubs database to a file using DAC application name pubs, version 1.2.3.4
$d.extract("c:\temp\pubs.dacpac", "pubs", "pubs", "1.2.3.4")

# Export schema and data from database pubsdac
$d.exportbacpac("c:\temp\pubsdac.bacpac", "pubsdac")

# Load dacpac from file & deploy to database named pubsnew
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load("c:\temp\pubs.dacpac")
$d.deploy($dp, "pubsnew")

# Load bacpac from file & import to database named pubsdac2
$bp = [Microsoft.SqlServer.Dac.BacPackage]::Load("c:\temp\pubsdac.bacpac")
$d.importbacpac($bp, "pubsdac2")

# clean up event
unregister-event -source "msg"

Now, the question for the reader. There has been a lot of work going on to make sets of APIs into libraries of cmdlets. I thought about this, but it seemed to be such a simple API that I'd be writing a cmdlet encapsulating 2-3 lines of code each time. So when is it useful to "cmdlet-ize" APIs? Always? Or is it too much to cmdlet-ize for every two lines of code? How about 5 lines of code? Is there a cutoff? Let me know on Twitter or in comments…

Thanks to those of you who attended the session.

@bobbeauch