Skip to content

Instantly share code, notes, and snippets.

Aaron Nelson SQLvariant

View GitHub Profile
SQLvariant / ClonePowerBI_WorkspaceUsers.PS1
Last active Aug 31, 2019
Copy Users & Groups from one Power BI Workspace to another
View ClonePowerBI_WorkspaceUsers.PS1
<# Clone Users from one Workspace to another
Start by specifying the Target Workspace
Then use the Out-GridView cmdlet to choose the Source Workspce to copy the users & roles from. #>
$TargetWorkspace = Get-PowerBIWorkspace -Name 'New QA Workspace';
(Get-PowerBIWorkspace |
Out-GridView -PassThru |
foreach { Get-PowerBIWorkspace -Id $_.Id -Scope Organization }).Users |
WHERE { $_.AccessRight -ne 'Viewer' } |
SQLvariant / CopyReport_PreserveDatasetConnection.ps1
Last active Sep 23, 2019
Promote a Power BI Report from a QA Workspace to a Prod Workspace, while preserving the connection to the Prod database when the updated report lands in the Prod Workspace.
View CopyReport_PreserveDatasetConnection.ps1
$source_workspace_ID = (Get-PowerBIWorkspace -Name 'QA-Workspace').Id
$report = Get-PowerBIReport -Name 'Regional Sales' -WorkspaceId $source_workspace_ID
$target_workspace_ID = (Get-PowerBIWorkspace -Name 'Prod-Workspace').Id
$targetReport = Get-PowerBIReport -Name 'Regional Sales' -WorkspaceId $target_workspace_ID
Copy-PowerBIReport -Report $report -WorkspaceId $source_workspace_ID -TargetWorkspaceId $target_workspace_ID -TargetDatasetId ($targetReport).DatasetId -OutVariable NewReport
<# Now to remove the old version of the report in the Prod workspace #>
#$url = '' + $target_workspace_ID + '/reports/' + $targetReport.Id
#Invoke-PowerBIRestMethod -Method Delete -Url $url
SQLvariant / Microsoft.PowerShellISE_profile.ps1
Last active Jun 23, 2020
The basic commands I always put in all of my PowerShell $Profile.CurrentUserAllHosts
View Microsoft.PowerShellISE_profile.ps1
Import-Module SqlServer;
cd $home\Documents\PoSh\Load\
. .\Get-History2.ps1;
cd C:\temp;
<# First we need an argument completer for -ServerInstance #>
Register-ArgumentCompleter -ParameterName ServerInstance -ScriptBlock {
(dir -Recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ |
?{ $_.Mode -ne 'd'} |
Group-Object ServerName).Name | ForEach-Object {
$CompletionText = $_
SQLvariant / TestContainer64.ipynb
Last active Jun 22, 2019
This is the container with the extra volume that won't restore databases. The SQL Notebook includes the error message I'm receiving.
View TestContainer64.ipynb
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
SQLvariant / Docker-Creation-Notebook.ipynb
Last active Jun 22, 2019
Test script for SQL 2019 CTP 3.0
View Docker-Creation-Notebook.ipynb
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
SQLvariant / ExtractISPAC_fromSSISDB.ps1
Created May 4, 2019
Extract an .ISPAC from an SSIS Catalog and write to disk via SSIS PowerShell Provider
View ExtractISPAC_fromSSISDB.ps1
<# This is the SSIS Project once it's deployed #>
$Proj = Get-Item 'SQLSERVER:\SSIS\localhost\SQL2017\Catalogs\SSISDB\Folders\ProviderSolution\Projects\TestSSISProject'
$Proj | Get-Member -MemberType Methods
<# This is the theory I have #>
[byte[]] $ProjBytes = $Proj.GetProjectBytes()
<# Dont run this piece of code, you will hate me. #>
SQLvariant / DeploySSIS_ISPac.ps1
Last active May 2, 2019
Deploy an .ISPAC file to a SSISDB Catalog using the SSIS Provider
View DeploySSIS_ISPac.ps1
# Variables
$TargetFolderName = "ProviderSolution"
$ProjectFilePath = "C:\temp\SSIS\TestSSISSolution\TestSSISProject\bin\Development\TestSSISProject.ispac"
$ProjectName = "TestSSISProject"
# Get the Integration Services catalog
$ProviderCatalog = Get-Item SQLSERVER:\SSIS\localhost\SQL2017\Catalogs\SSISDB\
# Create the target folder
New-Object "Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder" ($ProviderCatalog,
SQLvariant / BuildContainer_FromImage.ps1
Last active Apr 30, 2019
simple dockerfile for building a SQL-on-Linux container. Place this file in your c:\temp directory
View BuildContainer_FromImage.ps1
<# Step 0)
Put the dockerfile & AdventureWorks2016.bak into your c:\temp
$dockerfileURL = "";
$dockerfile = c:\temp\BuildContainer_FromImage.ps1;
Invoke-WebRequest -Uri $dockerfileURL -OutFile $dockerfile;
Copy-Item -Path "$($Home)\Downloads\AdventureWorks2016.bak" -Destination C:\temp
<# First, build the image #>
SQLvariant / Start-Pomodoro.ps1
Last active Apr 29, 2019
A PowerShell function to create Pomodoro timer. Defaults to 25 minute timer.
View Start-Pomodoro.ps1
<# PLEASE NOTE: I am not the original author of this function.
I found it online years ago, and have been using it ever since.
If you are the original author, please ping me and let me know,
so I can give you proper credit.
Based on another function in the PowerShell Gallery, the orginial author might be Nathan.Run()
Function Start-Pomodoro
Param (
SQLvariant / DockerDesktop-with-SQL-PowerShell-2.ps1
Created Apr 9, 2019
Create a SQL-on-Linux Docker Container with a Host Mount and Restore the 5 AdventureWorks Databases
View DockerDesktop-with-SQL-PowerShell-2.ps1
<# 0A) Before any of this can work, you must have Docker Destop running.
You must also have the latest SqlServer module installed from the PowerShell Gallery.#>
Install-Module SqlServer
<# 0B) Use this code to download the AdventureWorks2016.bak file from GitHub: #>
$BakURL = "";
$BakFile = "$($Home)/Downloads/AdventureWorks2016.bak";
Invoke-WebRequest -Uri $BakURL -OutFile $BakFile;
<# 1) Create a SQL-on-Linux Docker Container with just the code below.
You can’t perform that action at this time.