DLM Automation

This page shows you how to create a PowerShell script using the DLM Automation cmdlets that's equivalent to the SQL CI Build command.

The SQL CI Build command validates the schema in a scripts folder and adds it to a NuGet package. You can also use the Build command to add database documentation to the NuGet package, and to send the information in the package to DLM Dashboard.

Validating and packaging

This example uses the Build command in SQL CI to validate and package the schema in a scripts folder:

Command line example

sqlCI.exe Build /scriptsFolder=WidgetShop\Database\Scriptsfolder /packageId=WidgetShop /packageVersion=1.0 /outputFolder=c:\packages

Here's the same example using the DLM Automation PowerShell cmdlets:

PowerShell example

$scriptsFolder = ".\WidgetShop\Database\Scriptsfolder"
$validatedSchema = $scriptsFolder | Invoke-DlmDatabaseSchemaValidation
$databasePackage = $validatedSchema | New-DlmDatabasePackage -PackageId "WidgetShop" -PackageVersion "1.0"
$databasePackage | Export-DlmDatabasePackage -Path "C:\packages"

In the example above:

On line 2, Invoke-DlmDatabaseSchemaValidation validates the schema of the scripts folder.
On line 3, New-DlmDatabasePackage creates a NuGet package containing the validated schema.
On line 4, Export-DlmDatabasePackage exports the NuGet package to disk.

Including documentation

This example uses the SQL CI Build command to include database documentation in the NuGet package:

Command line example

sqlCI.exe Build /scriptsFolder=WidgetShop\Database\Scriptsfolder /packageId=WidgetShop /packageVersion=1.0 /outputFolder=c:\packages /includeDocs

Here's the same example using the DLM Automation PowerShell cmdlets:

PowerShell example

$scriptsFolder = ".\WidgetShop\Database\Scriptsfolder"
$validatedSchema = $scriptsFolder | Invoke-DlmDatabaseSchemaValidation
$schemaDocumentation = $validatedSchema | New-DlmDatabaseDocumentation
$databasePackage = $validatedSchema | New-DlmDatabasePackage -PackageId "WidgetShop" -PackageVersion "1.0" -Documentation $schemaDocumentation
$databasePackage | Export-DlmDatabasePackage -Path "C:\packages"

In the example above:

On line 3, New-DlmDatabaseDocumentation creates the database documentation for the validated schema.
On line 4, New-DlmDatabasePackage creates a NuGet package that contains the validated schema and the database documentation.

Sending information to DLM Dashboard

This example uses the SQL CI Build command to send schema and documentation information to DLM Dashboard:

Command line example

sqlCI.exe Build /scriptsFolder=WidgetShop\Database\Scriptsfolder /packageId=WidgetShop /packageVersion=1.0 /outputFolder=c:\packages /includeDocs /dlmDashboardHost=localhost /dlmDashboardPort=19528

Here's the same example using the DLM Automation PowerShell cmdlets:

PowerShell example

$scriptsFolder = ".\WidgetShop\Database\Scriptsfolder"
$validatedSchema = $scriptsFolder | Invoke-DlmDatabaseSchemaValidation
$schemaDocumentation = $validatedSchema | New-DlmDatabaseDocumentation
$databasePackage = $validatedSchema | New-DlmDatabasePackage -PackageId "WidgetShop" -PackageVersion "1.0" -Documentation $schemaDocumentation
$databasePackage | Export-DlmDatabasePackage -Path "C:\packages"
$databasePackage | Publish-DlmDatabasePackage -DlmDashboardUrl "http://localhost:19528"

In the example above, in line 6, the Publish-DlmDatabasePackage sends schema and documentation information to DLM Dashboard.

Specifying a temporary database

This example uses the SQL CI Build command with an existing temporary database:

Command line example

sqlCI.exe Build /scriptsFolder=WidgetShop\Database\Scriptsfolder /packageId=WidgetShop /packageVersion=1.0 /outputFolder=c:\packages /includeDocs /temporaryDatabaseServer="SQLServer2014" /temporaryDatabaseName=WidgetShop

Here's the same example using the DLM Automation PowerShell cmdlets:

PowerShell example

$scriptsFolder = ".\WidgetShop\Database\Scriptsfolder"
$temporaryDatabase = New-DlmDatabaseConnection -ServerInstance "SQLServer2014" -Database "WidgetShop"
$validatedSchema = $scriptsFolder | Invoke-DlmDatabaseSchemaValidation -TemporaryDatabase $temporaryDatabase
$schemaDocumentation = $validatedSchema | New-DlmDatabaseDocumentation -TemporaryDatabase $temporaryDatabase
$databasePackage = $validatedSchema | New-DlmDatabasePackage -PackageId "WidgetShop" -PackageVersion "1.0" -Documentation $schemaDocumentation
$databasePackage | Export-DlmDatabasePackage -Path "C:\packages"

In the example above:

On line 2, New-DlmDatabaseConnection creates the connection to the temporary database used for validating the schema.
On line 3, Invoke-DlmDatabaseSchemaValidation validates the schema of the scripts folder using the specified temporary database.
On line 4, New-DlmDatabaseDocumentation creates the database documentation for the validated schema using the specified temporary database.

DLM Automation

Switch from SQL CI Build command to PowerShell

Validating and packaging

Including documentation

Sending information to DLM Dashboard

Specifying a temporary database