Organizing Extended Continuous Integration Process with Data Import and Formatting of SQL Files

This topic provides an example of using PowerShell Cmdlets for organizing extended Continuous Integration process that includes the following stages:

  • Creation of a database connection.
  • Testing of the database connection.
  • Creation of a database with the ‘state based’ method. This method allows creating (or updating) a database with the help of the scripts ‘from scratch’. I.e. a database is specified in the script “as it has to be”, herewith practically all scripts are stored as the CREATE instructions.
  • Restoring unit tests on the database.
  • Filling the database with sample data using Data Generator.
  • Filling the database using data import from a file.
  • Running the tSQLt unit tests.
  • Formatting the scripts.
  • Publishing the package.
#region the variables

$serverName = "(LocalDb)\MSSQLLocalDB"
$databaseName = "TestDB"

# A script folder
$scriptFolder = "C:\Test\ScriptsFolder"

# A folder where the sql unit-tests are located, for example:
# ...\Test1.sql
# ...\Test2.sql
# ...\Test4.sql
$unitTestsFolder = "C:\Test\UnitTests"

# A filepath to a datagenerator file
$generatorFile = "C:\Test\TestData\data.dgen"

# A filepath to a dataimport file
$importDataFileName = "C:\Test\CSV\sample_data.csv"
$importTemplateFileName = "C:\Test\CSV\template.dit"

# An output report file for unit tests
$testReportOutputFileName = "C:\Test\TestReport\report.xml"

# A file that is created When publishing
$publishFileName = "Test.DevOpsAutomation.Database"

# A path for publishing
$publishOutputFolder = "C:\Test\NuGet\Repository"


#region the check function

Function check {

    if ($result) {
        Write-Host "Success" -ForegroundColor Green; Write-host ""
    else {
        Write-Host "Failed" -ForegroundColor Red;


#region CI pipeline

# Create database connection
Write-Host "Creating database connection..."
$connection = New-DevartSqlDatabaseConnection -Server $serverName -Database $databaseName -WindowsAuthentication $true

# Test database connection
Write-Host "Testing database connection..."
$result = Test-DevartDatabaseConnection -Connection $connection;

# Recreating database
Write-Host "Recreating database..."
$project = Invoke-DevartDatabaseBuild -SourceScriptsFolder $scriptFolder -Connection $connection

# Restore unit tests on the database
Write-Host "Restoring unit tests..."
$result = Invoke-DevartExecuteScript -Input $unitTestsFolder -Connection $connection

# Fill database with sample data and run unit tests
Write-Host "Filling database with sample data and running unit tests..."
$result = Invoke-DevartDatabaseTests -InputObject $connection -InstalltSQLtFramework -IncludeTestData -DataGeneratorProject $generatorFile -OutReportFileName $testReportOutputFileName -ReportFormat JUnit -RewriteReport

# Import sample data from a file to certain table
Write-Host "Importing sample data from a file..."
$result = Invoke-DevartDataImport -InputFile $importDataFileName -TemplateFile $importTemplateFileName

# Format scripts
Write-Host "Formatting migration scripts..."
$result = Invoke-DevartFormatScript -Source $migrationScriptsFolder

# Publish project
Write-Host "Publishing project..."
Set-DevartPackageInfo -Project $project -Id $publishFileName -Version 3.0.0
Publish-DevartDatabaseProject -Project $project -Repository $publishOutputFolder -AutoIncrementVersion