After several months of planning and preparation (…just on my part – organizers spent much longer), SQLBits 2023 came and went this past week at the ICC Wales, near the beautiful community of Newport. This was my first trip to SQLBits, and I didn’t know what to expect. It was a really great conference, from the speakers and topics to the organization and logistics of running such a large event. It was also a time to meet new people and connect with others in the SQL Server community.

What did I learn this week did you ask?

#1 – Rob Sewell and Ben Weissman have amazing suits! Not many could pull it off, but they…well, it’s not that bad!

#2 – Erin Stellato reminded me about Extended Events in SQL Server

Admittedly, I either try to brute force my way through issues, and/or, “try it again,” hoping it was an intermittent issue. When I do look for more help, I tend to go to the Query Store, and then on to Profiler to get more detail about what’s going on. What I didn’t know, is there are 180 events in Profiler to track. I mean, there are only 180 events. According to Erin, in SQL Server 2022, there are closer to 2,000 events to track!! Also, different extended events can be tracked with different filters, to get what need more quickly. Next time my developers say the SQL instance is slow, Extended Events will be fired up.

#3 – Microsoft Purview and Azure Synapse Analytics

I need to learn a lot more about these 2 products. My manager keeps telling me Purview can do this, and it can do that. Can it really?? After this week at SQLBits, I believe it can do those things, and a lot more. My company has grown quickly over the years, and a lot of key data is in Excel spreadsheets, CSV files, etc. Knowing who owns those files and that they understand the information the files provide is a really important thing to us. Purview gives us that ability…I think. I need to read more…

Azure Synapse Analytics will also play a large role, maybe with DataVerse, maybe not. By providing Azure Synapse Analytics the data to store in a data lake, Spark and DataBricks can be added on top to query the data. PowerBI then sits on top of all of that, providing visualizations of the data our users really need.

So much more there to discover…

#4 – A new type of Database Project is coming

For the past 6 – 10 years, Microsoft has had a project type in Visual Studio to store state-based SQL Server database schemas. Ideally, when a new project is started, the new database will be started in the project and saved under source control. What’s more realistic is when the database gets to UAT or production, then a schema comparison is used to load the full database schema into the project.

With Azure Data Studio and Visual Studio Code, there are extensions to manage schema changes for SQL Server/Azure SQL DB/Azure SQL Managed Instance. Both of these extensions will “compile” into a DACPAC, which is deployed to the database with sqlpackage.exe.

As I continue to refine my workshop to deploy database changes with Azure DevOps (and GitHub), I’ll be moving over to these 2 new tools.

There’s more to share, certainly, from SQLBits. Keep an eye out for the next post…

For those who attended my session, “Introducing Automated Deployments with Azure DevOps,” thank you. I think we had a good discussion on multiple pieces covered in the slides.

Speaking of the slides, here is a link to them – https://endrephome.files.wordpress.com/2023/03/sqlsaturdayaustin2023-devops.pptx

In the main demo, a deployment pipeline was created to build AND release code to an Azure SQL database. Below is the pipeline as we ended it. There was a question about deploying database schema changes to on-premises SQL Servers. There are tasks available in the Marketplace that will do this. It can also be accomplished through the use of a PowerShell script.

Using the pipeline above to deploy locally, there are a few changes required. First, instead of use Azure SQL Database deployment for the final task, Add a PowerShell script, like was done in the build section of this pipeline. The script will be inline and should look similar to this snippet (the options should all be on a single line with the command – this is for readability). The command sqlpackage.exe will be executed with an Action of publish. More information regarding the syntax for sqlpackage.exe can be found here.

sqlpackage.exe /Action:publish 
    /SourceFile:$(Pipeline.Workspace)\WorldWideImporters.dacpac 
    /TargetServerName:myserver.mydomain.com
    /TargetDatabaseName:WideWorldImporters
    /TargetEncryptConnection:false

What was not mentioned in the session is the PowerShell script needs to be able to connect to your on-premises SQL Server. To do that, an agent will be needed on your network and registered with Azure DevOps. Defining an agent pool is beyond the scope of this post, but this is documentation I used to create my pool . Below is what my pool, WorkshopPool, has for agents (Yes, I have 4 agents for workshops; you only need 1 agent in a pool).

In the pipeline’s deployment job, below, the properties need to be to adjusted. Instead of inheriting the agent from the pipeline, the Agent pool needs to be the one created for your on-premises agent. When the pipeline runs, this job will run on your agent and log into the SQL Server instance and deploy the DACPAC.

With a little luck the first time and knowledge on subsequent setups, on-premises SQL Server database deployments should become reasonably easy, though probably never as easy as deploying to Azure SQL DB or Azure SQL Managed Instance.

Recently, I was working with a release pipeline to add our Azure Data Factory solution. This was the first attempt to release Azure Data Factory components. Of course, there were many issues to resolve. The release pipeline handles both databases from multiple Git repositories and now ADF code, so it’s no longer an out of the box solution.

Microsoft provides documentation for AzureResourceManagerTemplateDeployment@3, which is helpful, if you read it thoroughly. Of course, I did not read it thoroughly, so I didn’t see the note about the DeploymentMode options. With the different issues, I decided to set parameter to ‘Complete’, instead of the default value, ‘Incremental’. The task looked a lot like the example below.

   - task: AzureResourceManagerTemplateDeployment@3
      displayName: 'Deploy ADF Solution'
      inputs:
        deploymentScope: 'Resource Group'
        azureResourceManagerConnection: '$(azureSubscription)'
        subscriptionId: '<subscriptionID>'
        action: 'Create Or Update Resource Group'
        location: '<Azure region>'
        resourceGroupName: '<resource group>'
        templateLocation: 'Linked artifact'
        csmFile: '$(System.ArtifactsDirectory)/<SolutionPath>/ARMTemplateForFactory.json'
        csmParametersFile: '$(System.ArtifactsDirectory)/<SolutionPath>/ARMTemplateParametersForFactory.json'
        overrideParameters: -factoryName xxxxxxxxxxxxx
                            -KeyVault_properties_typeProperties_baseUrl $(KeyVault-AutomatedTesting)
                            -ADLS_Storage_properties_typeProperties_url $(ADLSLocation-AutomatedTesting)
        deploymentMode: Complete

Oops! That was a mistake. The deployment failed, with a new error message about the data factory not being found. Excuse me?! I just saw it a couple minutes ago. Going back to Azure Data Factory, the instance I had been working with was no longer listed. What happened in the last couple minutes? The only thing I did was change the DeploymentMode to ‘Complete’.

Referring back to the documentation, I found this little nugget.

Complete mode deletes resources that are not in your template. Complete mode takes relatively more time than incremental mode. If the task times out, consider increasing the timeout or changing to the Incremental mode.

My ARM template only included the the code contained within Azure Data Factory. It did not contain the definition of the data factory itself. As a result, the Complete deployment mode deleted the data factory (along with a storage account) and then tried to deploy the code. Of course this didn’t work.

Fortunately, a proof-of-concept environment was created to develop the release pipeline, so I was the only one using the instances that were deleted. This mistake is a good reminder why resource groups should be resource instance specific and not encompass a large number (or even a modest number) of resources. It’s also a reminder to read the documentation, when new features are being used. ADO Pipeline tasks (and jobs) are concisely defined in YAML, but they perform a lot of work. It is easy to run into unexpected outcomes.

Data Saturday Atlanta was a major success, both for the event and for myself. Rie Merritt (@IrishSQL) and the organizing committee did a great job bringing together a stellar group of speakers who covered many great topics. A HUGE thank you to Atlanta Azure Data User Group for the opportunity to speak about PowerShell. Thank you to each and every attendee of my session, Reducing Data Warehouse Processing with PowerShell Pre-ETL Processing. It was great to have so many people show interest in PowerShell and how data developers, data analysts, QA analysts, and others can leverage its capabilities.

The PDF version of the slide deck is available for download.

In addition to the slides, below are the demo scripts from the presentation. The data files used in the presentation are being made available, but you really shouldn’t need them. The idea is to use the scripts to get inspiration for your own solutions. The examples make use of two functions I’ve created. They are documented in this post.

CSV Validation – Loading data from a CSV into SQL Server is straightforward, if the columns in the CSV file match the database table’s. This validation performs a cursory check of the columns being passed in a CSV file. Certainly, there are other checks that can be performed, but these are a couple to a solution started.

# It is assumed the CSV being used contains a header record in the first row of the file. As a result,
# only the first record ( -TotalCount 1 ) is being read.

$header = Get-Content -Path $FileName -TotalCount 1
# Add .Replace("`"", "") to the end of the split operation to remove quotes from the values.
$headerColumns = ($header -split ',') 


# With a database table designed with the schema intended to be in the CSV 
#   file, query SQL Server to get the list of columns (and data types).
# NULL is being passed to the -Credential parameter, which informs the 
#   function to use Windows authentication to connect to the SQL instance.
$databaseTableColumns= Get-SQLColumnsForDataTable `
                -ServerInstance localhost `
                -Database AdventureWorks `
                -Credential $null `
                -SchemaName Production `
                -TableName Product

# Perform a quick column count to see if the number of columns match.  By no means does this mean the columns are the same, even if the counts are equal.
if ($headerColumns.Count -eq $columns.Count)
{
    write-output "The columns in the file match the table definition"
}
else
{
    write-output "The number of columns in the source file does not match the table definition"
}

# Check the CSV's columns to see if columns from the table are missing.
$missingColumn = $false
foreach ($column in $columns) 
{
    if ($headerColumns -notcontains $column.ColumnName) 
    { 
        $missingColumn = $true
        write-output "   Column, $($column.ColumnName), was not found" 
    } 
}

Excel Validation – Like the CSV validation, validating Excel Files is also possible with PSExcel module (technically on EPPlus.dll is being used for the OfficeOpenXML namespace). As you compare the two scripts, there is more work in the Excel validation, due to needing the assistance of EPPluss.dll to access the data, versus reading the first line of text from the CSV file with Get-Content.

# Import the PSExcel module from the PowerShell Gallery; only needs to be run once on a computer.  
# Run the PowerShell session in administrator mode to install the module for all users.

install-module PSExcel

import-module PSExcel

$excelFile = New-Object OfficeOpenXml.ExcelPackage $FileName
$workbook  = $excelFile.Workbook

# Every worksheet in Excel has a name on the tab.  That name goes here.
$worksheet = $workbook.Worksheets["Details"]
 
# $worksheet.Dimension.Rows returns the number of records in the Excel worksheet.  
# It may be useful to log the number of records received in a pre-processing/validation step.
Write-Output "#################"
Write-Output "   File contains $($worksheet.Dimension.Rows) rows"
Write-Output "#################"

# Like Dimension.Rows, Dimension.Columns returns the number of columns with data in the worksheet.
$worksheetColumns = $worksheet.Dimension.Columns

 #
 # Get columns for Sales.SalesOrderDetails
 #
 $dbColumns = Get-SQLColumnsForDataTable -ServerInstance localhost `
                                         -Database AdventureWorks `
                                         -Credential $null `
                                         -SchemaName "Sales" `
                                         -TableName "SalesOrderDetail"

if ($worksheetColumns -eq $dbColumns.Count)
{
    write-output "             "
    write-output "The Excel file and the database table have the same number of columns"
    write-output "             "
}
else
{
    write-output "The Excel file and the database table do not have the same number of columns"

    #
    # Find the differing columns
    #
    $worksheetColumnNames = @()
    $dbColumnNames = $dbColumns.ColumnName
    foreach ($columnNum in 1..($worksheetColumns-1))
    {
        $columnName = $worksheet.Cells.Item(1,$columnNum).Text
        
        if ($dbColumns.ColumnName -notcontains $columnName)
        {
            write-output "$columnName is not in the database table"
        }

        $worksheetColumnNames += $columnName
    }
}

JSON to CSV Transformation – This script is directly related to the screenshot of the JSON file from slide 21 in the slide deck, creating a CSV file from the highlighted elements in the JSON.

$activities= (Get-Content $FileName | convertfrom-json).Value

$activitiesTable = $activities | select activityName, activityRunStart, activityRunEnd, durationInMs, 
                                  @{Name="storedProcedureName";
                                    Expression={
                                                    ($_ | select-object -expandProperty input).storedProcedureName
                                               }
                                   }

$activitiesTable | format-table

$activitiesTable | export-csv c:\demos\activities.csv -NoTypeInformation

Using Metadata to Load Tables – When you need to load files into tables repeatedly over time, such as a updated customer information on a monthly basis, it may be useful to create a table to define metadata about those files. This could includ the naming pattern of the file, which database the data should reside in, along with the specific schema and table names. This example takes that metadata and instantiates a bulk copy object from Microsoft.Data.SqlClient. With the System.Data.DataTable object holding the contents of the CSV file, that data is loaded into the specified table.

$query = "select FileNamePattern, 
                 DestinationDatabaseName, 
                 DestinationSchemaName, 
                 DestinationTableName, 
                 DestinationSchemaTableName 
          from Logging.LoadMetadata"

# invoke-sqlcmd with no credential uses context of the current user
$files = invoke-sqlcmd -ServerInstance localhost -Database PowerShellDemo -Query $query 

foreach ($file in $files)
{
    write-output "...Loading $($file.DestinationSchemaTableName)"

    #Query sys.columns & sys.types to get the fields of a table
    $dbColumns = Get-SQLColumnsForDataTable -ServerInstance localhost `
                                            -Credential $null `
                                            -Database $file.DestinationDatabaseName `
                                            -SchemaName $file.DestinationSchemaName `
                                            -TableName $file.DestinationTableName

    #Load CSV data into memory and generate a DataTable with defined data types
    $dataTable = Import-CSV "C:\Demos\CSVs\$($file.FileNamePattern)"  | Out-DataTableWithDBDataTypes -Columns $columns

    # Bulk copy the DataTable into the specified table
    $bulkCopy = new-object ([Microsoft.Data.SqlClient.SqlBulkCopy]) `
                    -ArgumentList "server=localhost;database=$($file.DestinationDatabaseName);Integrated Security=SSPI"
    $bulkCopy.DestinationTableName = $file.DestinationSchemaTableName
    $bulkCopy.WriteToServer($dataTable)
}

If you have any questions about these demo scripts, you can find me on Twitter, @em_dempster.

PowerShell has a lot of great functionality to pull data from various sources on a server, like event logs, WMI objects, Registry, etc. However, preparing data for logging in a database is an area PowerShell is lacking.

As supporting material for SQL Saturday/Data Saturday presentations, here are a couple functions, with basic functionality, to aid in scripting bulk loads to SQL Server. If a different database platform is being used, such as PostgreSQL, Oracle, etc., this code will need to be updated to support that platform.

Get-SQLColumnsForDataTable, as the name should imply, returns an array of objects representing the column names and data types from a table in a SQL Server database. The object array can be used by the second function, Out-DataTableWithDataTypes to predefine the data types of columns in a System.Data.DataTable object. This does not return the length of strings or the scale and precision of decimal and numeric data types. Additionally, the query translates common SQL data types to .Net data types. If the table includes binary, image, CLR data types, etc., those data types will need to be translated to an equivalent .Net data type.

function Get-SQLColumnsForDataTable
{
        Param(
		[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
		[string]$ServerInstance,
		[Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
		[string]$Database,
		[Parameter(Mandatory=$false, ValueFromPipelineByPropertyName=$true)]
		[PSCredential]$Credential,
        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [String] $SchemaName,
        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [String] $TableName
        )
    $Query = "SELECT c.Name ColumnName, ty.Name DataType, case when ty.Name like '%char' then 'String' 
                                                               when ty.Name = 'int' then 'Int32' 
                                                               when ty.name = 'Integer' then 'Int32' 
                                                               when ty.name = 'Smallint' then 'Int16' 
                                                               when ty.Name = 'BigInt' then 'Int64' 
                                                               when ty.name = 'money' then 'Decimal' 
                                                               when ty.name = 'numeric' then 'Decimal' 
                                                               when ty.name = 'float' then 'Decimal'
                                                               when ty.name = 'date' then 'DateTime'
                                                               when ty.name = 'bit' then 'Boolean'
                                                               else ty.name 
                                                          end DataTableType
    FROM sys.tables t
	    INNER JOIN sys.columns c ON c.object_id = t.object_id
	    INNER JOIN sys.types ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
		left JOIN sys.identity_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
    WHERE t.name = '$TableName' AND t.schema_id =SCHEMA_ID('$SchemaName') "

    if ($Credential -eq $null)
    {
	    $columns = invoke-sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query
    }
    else
    {
        $columns = invoke-sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query -Username $Credential.UserName -Password $Credential.GetNetworkCredential().Password
    }

    return , $columns
}

Out-DataTableWithDataTypes has been derived from code by Chad Miller and taken over by Rambling Cookie Monster (GitHub repository link). The original code attempted to determine the data type of incoming object. However, the data is loaded into the object as strings, so that’s the only data type that can be determined. This adaptation adds a second input parameter, $Columns. The best way to define this parameter is with the function above, Get-SQLColumnsForDataTable.

function Out-DataTableWithDataTypes
{ 
    [CmdletBinding()] 
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject,
          [Parameter(Position=1, Mandatory=$true)] [PSObject[]]$Columns)
 
    Begin 
    { 
        
        $dt = new-object Data.datatable 
        if ($columns.Count -gt 0)
        {
            foreach($column in $Columns)
            {
                [void]$dt.Columns.Add($column.ColumnName, $column.DataTableType)
            }
        }
    } 
    Process 
    { 
        foreach ($object in $InputObject) 
        { 
            $DR = $DT.NewRow()   
            foreach($property in $object.PsObject.get_properties()) 
            {     
                if ($DT.Columns.Contains($property.Name))
                {
                    if ($property.value -eq $null -or $property.Value -eq "" -or $property.Value -eq "NULL")
                    {
                        $DR.Item($property.Name) = [System.DBNull]::Value
                    }
                    else 
                    {
                        $DR.Item($property.Name) = $property.value
                    }
                } 
            }   
            $DT.Rows.Add($DR)  
             
        } 
    }  
      
    End 
    { 
        Write-Output @(,($dt)) 
    } 
 
}

With these 2 functions, the basic functionality is there to load data into a SQL Server database table with the proper data types. As with any data loading process, if the data types do not match, Out-DataTableWithDataTypes will fail on the load.

As with most every blog on the Internet, this code has been working for me and the needs I have for it. I fully recognize this does not meet every scenario. Your use of this code is welcome, but there are no guarantees with this code.

As a PowerShell developer (yes, I consider PowerShell coding development, when it’s for pre-ETL processing), there are a handful of very useful namespaces .Net.

System.Data – “basic” data containers are classes in this namespace, include DataTable and DataSet. These are generic types, regardless of source or destination database or data source.

System.XML – If you’re ingesting XML data, there are several classes here you’ll use. It’s likely you’ll use XmlReader to stream data or XmlDocument to load a document into memory to read or manipulate.

Microsoft.Data.SqlClient – Updated version to System.Data.SqlClient, used to access SQL Server, including Azure SQL Databases and Azure SQL Managed Instances. SqlConnection, SqlCommand and SqlBulkCopy are a data developer’s best friends.

OfficeOpenXML.Core.ExcelPackage – Working with XLSX files requires an assembly found in the PowerShell Gallery, among other places. Classes in this assembly allow developers to read, update and write Excel data.

This is a placeholder and this post will be updated at a later date…