SqlBIManager.psm1
|
$Script:asServer = $null $Script:LogVerbose = $false $Script:LogInformation = $true $Script:LogWarning = $true $Script:SBMLogFilePath = $null #region Logging function Use-SbmLog{ param( [string]$FilePath, [switch]$LogVerbose, [switch]$IgnoreInformation, [switch]$IgnoreWarning ) $Script:LogVerbose = $LogVerbose $Script:LogInformation = !$IgnoreInformation $Script:LogWarning = !$IgnoreInformation $Script:SBMLogFilePath = $FilePath } function Write-SbmLogVerbose{ param([string] $Message) Write-Verbose $Message if ($Script:LogVerbose) { if ($Script:SBMLogFilePath){ $Message | Out-File FilePath $Script:SBMLogFilePath -Append -Force } } } function Write-SbmLogInformation{ param([string] $Message) Write-Information $Message if ($Script:LogInformation) { if ($Script:SBMLogFilePath){ $Message | Out-File FilePath $Script:SBMLogFilePath -Append -Force } } } function Write-SbmLogWarning{ param([string] $Message) Write-Warning $Message if ($Script:LogWarning) { if ($Script:SBMLogFilePath){ $Message | Out-File FilePath $Script:SBMLogFilePath -Append -Force } } } function Write-SbmLogError{ param([string] $Message) Write-Error $Message if ($Script:SBMLogFilePath){ $Message | Out-File FilePath $Script:SBMLogFilePath -Append -Force } } #endregion #region SSAS function Connect-ASServer{ <# .SYNOPSIS Connect to Analysis Services instance .PARAMETER ASInstance Server name or server name with instance name .PARAMETER ConnectionOptions Optional string added to the connection string .EXAMPLE Connect-ASServer MyAsServer .EXAMPLE Connect-ASServer mySqlSerer.Internal.MyDomain.com\TABULAR #> [CmdletBinding(DefaultParameterSetName = 'default')] param( [parameter(Mandatory=$true)][string]$ASInstance, [string] $ConnectionOptions ) if ($Script:asServer.Connected) { $Script:asServer.Disconnect() } # Create a connection to the server instance $connString = "Provider=MSOLAP;DataSource=" + $ASInstance + ";" + $ConnectionOptions $Script:asServer = New-Object -TypeName Microsoft.AnalysisServices.Server $Script:asServer.Connect($connString) Write-SbmLogInformation "Connected to instance $ASInstance ($($asServer.ServerMode))" } function ConvertTo-AsmlDatabase { <# .SYNOPSIS Gets the meta data for a database. .DESCRIPTION Creates a Analysis server Meta Language database object for a database on the currently connected AS server. .PARAMETER Database ASDatabase object (from Get-ASDatabase). Accepts input from pipeline. .EXAMPLE #Exports all databases on the server Connect-ASServer "myServer\mySsasTabInstancer" Get-ASDatabase | ConvertTo-AsmlDatabase | Write-AsmlDatabase -ScriptFolder "C:\Sources\ASDatabases" #> param( [parameter(ValueFromPipeline, Mandatory=$true)][object] $Database ) Process { $jscript = [Microsoft.AnalysisServices.Tabular.JsonScripter]::ScriptCreateOrReplace($Database) $createorReplace = ConvertFrom-Json $jscript Write-Output $createorReplace.createOrReplace.database } } function Get-ASDatabase { <# .SYNOPSIS Get List of all databases on the currently connected Analysis Services server. #> $script:asServer.Databases } function Clear-ASDatabase { <# .SYNOPSIS Clears the data in a Analysis Server database. .DESCRIPTION Executes a process ClearValues on the currently connected server. .PARAMETER Database ASDatabase object (from Get-ASDatabase). Accepts input from pipeline. .EXAMPLE #Clears data in all databases on the server Connect-ASServer "myServer\mySsasTabInstancer" Get-ASDatabase | Clear-ASDatabase .EXAMPLE #Clears data in a specified database on the server Connect-ASServer "myServer\mySsasTabInstancer" Clear-ASDatabase -Name TestDatabase #> param( [parameter(ValueFromPipeline, Mandatory=$true, ParameterSetName="ByObject")][PSObject]$Database, [parameter(Mandatory=$true, ParameterSetName="ByName")][string] $Name ) Process { if ($PSBoundParameters.ContainsKey("Database")) { $Name = $Database.Name } $processDatabase = [PSCustomObject]@{ "refresh"= [PSCustomObject]@{ "type" = "clearValues" "objects"= @([PSCustomObject]@{ "database" = $Name}) }} $ScriptContent = ConvertTo-Json $processDatabase -Depth 25 Write-SbmLogInformation "Refresh clearValues $($Database.name)" $result = $script:asServer.Execute($scriptcontent) if ($result.Messages.Count -gt 0) { Write-SbmLogError("Error Clear-ASDatabase $($Name). $($result.Messages[0].Description)") } } } function Publish-AsmlDatabase { <# .SYNOPSIS Deploy Analysis Server Meta Language database object to server. .DESCRIPTION Generates CreateOrReplace script from asdatabase definition and executes on currently connected server. .PARAMETER Database ASML database object (deserialized from asdatabase file). Accepts input from pipeline. .PARAMETER ClearValues Execute process clear values before altering the database. Prevents failure from inconsistent existing data. Ignore if the database doesn't already exist. .EXAMPLE Get-ChildItem C:\MySources\Release1 -Filter "*.asdata" | Read-AsmlDatabase | Publish-ASDatabase .EXAMPLE Publish-ASDatabase -Database $TestDatabase -ClearValues #> param( [parameter(ValueFromPipeline, Mandatory=$true)][PSObject]$Database, [switch] $ClearValues ) Begin { #get list of databases alreday on target server $dbs = Get-ASDatabase } Process { if ($ClearValues) { #check if database already exists if ($dbs.Name -contains $Database.name) { Clear-ASDatabase -Name $Database.name } } $createorReplace = [PSCustomObject]@{ "createOrReplace"= [PSCustomObject]@{ "object"= [PSCustomObject]@{ "database" = $Database.name}; "database" = $Database }} $ScriptContent = ConvertTo-Json $createorReplace -Depth 25 Write-SbmLogInformation "Create or replace $($Database.name)" $result = $script:asServer.Execute($scriptcontent) if ($result.Messages.Count -gt 0) { Write-SbmLogError("Error Clear-ASDatabase $($Name). $($result.Messages[0].Description)") } } } function Read-AsmlDatabase { <# .SYNOPSIS Create object from file. .DESCRIPTION Reads json file (default extension: asdatabase) en serializes it to a (Analysis Server Meta Language) database object. .PARAMETER File Full path to .database file. .EXAMPLE $myDb = Read-AsmlDatabase -File "C:\Sources\ASTabularDatabase\MyDb.asdatabase" .EXAMPLE #Deploy all datases in folder to server Get-ChildItem C:\MySources\Release1 -Filter "*.asdata" | Read-AsmlDatabase | Publish-ASDatabase #> param( [parameter(ValueFromPipelineByPropertyName,Mandatory=$true)][Alias('PSPath')][string]$File ) Process { $ScriptPath = Convert-Path $File $scriptcontent = [System.IO.File]::ReadAllText($ScriptPath) $jsonObject = ConvertFrom-Json -InputObject $ScriptContent Write-Output $jsonObject } } function Write-AsmlDatabase { <# .SYNOPSIS Serialize database object to file. .DESCRIPTION Serializes a (Analysis Server Meta Language) database object to a JSON string and saves it to a file. The databas name will be used as file name. .PARAMETER Database Analysis Server Meta Language database object .PARAMETER ScriptFolder The folder the file is written to .PARAMETER FileExtension The file extension. If not supplied .asdatabase is used. .EXAMPLE #Exports all databases on the server Connect-ASServer "myServer\mySsasTabInstancer" Get-ASDatabase | ConvertTo-AsmlDatabase | Write-AsmlDatabase -ScriptFolder "C:\Sources\ASDatabases" #> param( [parameter(ValueFromPipeline,Mandatory=$true)][PSObject]$Database, [Parameter(Mandatory=$true)][string] $ScriptFolder, [string] $FileExtension = "asdatabase" ) Process { $ScriptContent = ConvertTo-Json $Database -Depth 25 [System.IO.File]::WriteAllText([System.IO.Path]::Combine( $ScriptFolder,$Database.name + "." + $FileExtension),$scriptcontent) } } function Write-ASDatabaseCreateReplaceScript { <# .SYNOPSIS creates a CreateOrReplace script from a database object. .DESCRIPTION Serializes a (Analysis Server Meta Language) database object to a JSON string and wraps it in a createOrReplace script syntax. The script name will be used as file name. .PARAMETER Database Analysis Server Meta Language database object .PARAMETER ScriptFolder The folder the file is written to .PARAMETER FileExtension The file extension. If not supplied .jsona is used. #> param( [parameter(ValueFromPipeline,Mandatory=$true)][PSObject]$Database, [Parameter(Mandatory=$true)][string] $ScriptFolder, [string] $FileExtension = "jsona" ) Process { $createorReplace = [PSCustomObject]@{ "createOrReplace"= [PSCustomObject]@{ "object"= [PSCustomObject]@{ "database" = $Database.name}; "database" = $Database }} $ScriptContent = ConvertTo-Json $createorReplace -Depth 25 [System.IO.File]::WriteAllText([System.IO.Path]::Combine( $ScriptFolder,$Database.name + "." + $FileExtension),$scriptcontent) } } <# .SYNOPSIS Edit data source details on an anlysis services database. .PARAMETER Database Analysis Server Meta Language database object. .PARAMETER Name Name of the data source in the database. .PARAMETER ConnectionString New connection string for the data source. .PARAMETER ImpersonationMode New ImpersonationMode for the data source. #> function Set-AsmlDatasource { [CmdletBinding(SupportsShouldProcess = $true ,ConfirmImpact='Medium',DefaultParameterSetName = 'default')] param( [parameter(ValueFromPipeline,Mandatory=$true)][PSObject]$Database, [string] $Name, [string] $ConnectionString, [string] $ImpersonationMode ) Process { $datasource = $Database.model.dataSources | Where-Object name -eq $Name if (!$datasource) { Write-SbmLogVerbose "Datasource $($Name) not found." } else { if ($PSCmdlet.ShouldProcess("Alter Datasource $($Name).")){ Write-SbmLogInformation "Altering Datasource $($Name)." if ($PSBoundParameters.ContainsKey('ConnectionString')) { $datasource.connectionString = $ConnectionString } if ($PSBoundParameters.ContainsKey('ImpersonationMode')) { $datasource.impersonationMode=$ImpersonationMode } } } Write-Output $Database } } <# .SYNOPSIS Add or Replace a member in an Analysis Services database role. .DESCRIPTION Add a new User or Group to a role, or replace an existing one. .PARAMETER Database Analysis Server Meta Language database object. .PARAMETER RoleName Analysis Services database role name .PARAMETER Member AsmlRoleMember object containing AD user or Group SID and name. .PARAMETER ReplaceMember Optional AsmlRoleMember object containing AD user or Group SID and name for the member to replace. .PARAMETER Filter Optional Filter expression evaluated for every database/role. If filter evaluates to false, member is not added. .PARAMETER FilterArgumentList Optional arguments evaluated in the filter. #> function Add-AsmlRoleMember { param( [parameter(ValueFromPipeline,Mandatory=$true)][PSObject]$Database, [Parameter(Mandatory=$true)][string] $RoleName, [Parameter(Mandatory=$true)][PSObject] $Member, [PSObject] $ReplaceMember, [ScriptBlock] $Filter, [Object[]] $FilterArgumentList ) Process { $roles = $Database.model.roles if ($PSBoundParameters.ContainsKey("RoleName")) { $roles = $roles | Where-Object name -eq $RoleName } Foreach ($role in $roles) { $filterResult = $true if ($PSBoundParameters.ContainsKey("Filter")) { $filterInput = [PSCustomObject] @{ "Database"=$Database "Role"=$role } $filterResult = Invoke-Command -ScriptBlock $Filter -ArgumentList $FilterArgumentList -InputObject $filterInput } if ($filterResult -eq $true) { if ($PSBoundParameters.ContainsKey("ReplaceMember")) { if($role.members.memberId -contains $ReplaceMember.memberId) { Write-SbmLogInformation "Replacing member with $($Member.memberName) in role $($role.name)." $crm = $role.members | Where-Object memberId -eq $ReplaceMember.memberId $crm.memberId = $Member.memberId $crm.memberName = $Member.memberName } } else { Write-SbmLogInformation "Adding member $($Member.memberName) to role $($role.name)." #Does the memberscollection exist? if ($role.PSObject.Properties.match("Members").Count -eq 0) { $_membersColection = @($Member) $role | Add-Member "members" $_membersColection } else { $role.Members+=$Member } } } } Write-Output $Database } } #endregion #region SSAS ActiveDirectory function ConvertTo-AsmlRoleMember <# .SYNOPSIS Create AsmlRoleMember object from ActiveDirectory group or user .PARAMETER ADObject ActiveDirectory User or Group object .PARAMETER Domain Domain as in Domanin\AccountName #> { param( [parameter(Mandatory=$true,ValueFromPipeline)][object] $ADObject, [Parameter(Mandatory=$true)][string] $Domain ) Process { $rm = [PSCustomObject]@{ "memberId"=$ADObject.SID.value "memberName"= "$($Domain)\$($ASObject.SamAccountName)" } if ($PSCmdlet.MyInvocation.ExpectingInput) { Write-Output $rm } } End { if (!$PSCmdlet.MyInvocation.ExpectingInput) { $rm } } } function Select-AsmlRoleMember { <# .SYNOPSIS Get ActiveDirectory group or user and create AsmlRoleMember object .PARAMETER Domain Domain as in Domanin\AccountName .PARAMETER ADGroup ActiveDirectory Group Identity .PARAMETER ADUser ActiveDirectory User Identity #> param( [Parameter(Mandatory=$true, ParameterSetName="ADGroup")] [Parameter(Mandatory=$true, ParameterSetName="ADUser")][string] $Domain, [Parameter(Mandatory=$true, ParameterSetName="ADGroup")][string] $ADGroup, [Parameter(Mandatory=$true, ParameterSetName="ADUser")][string] $ADUser ) $adinfo = $null if ($PSBoundParameters.ContainsKey("ADGroup")) { $adinfo = Get-ADGroup -Identity $ADGroup } else { $adinfo = Get-ADUser -Identity $ADUser } ConvertTo-ASMLRoleMember -ADObject $adinfo -Domain $Domain } #endregion #region SSIS function Connect-SsisCatalog{ <# .SYNOPSIS Connect to the SSIS catalog .PARAMETER Server The SQL Server instance that contains the catalog database. .PARAMETER Catalog The SSIS catalog database name. if omitted, SSISDB is assumed .EXAMPLE Connect-SsisCatalog MyServerName\MySqlInstance #> param( [Parameter(Mandatory=$true)][string]$Server, [string]$Catalog="SSISDB" ) # Create a connection to the server $SqlConnectionstring = "Data Source=" + $Server + ";Initial Catalog=master;Integrated Security=SSPI;" $script:isSqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring try{ $script:isSqlConnection.Open() } catch{ Write-SbmLogError "Error Connection to $($Server). Details: $($_.Exception.Message)" return } #check if the catalog exists $script:isCatalog = $Catalog try { $sqlStatement = "SELECT COUNT(1) FolderCount FROM [$($script:isCatalog)].[catalog].[folders]" $sqlCmd = New-Object System.Data.SqlClient.SqlCommand $sqlStatement , $script:isSqlConnection $foldercount = $sqlCmd.ExecuteScalar() Write-SbmLogInformation "Connected to Catalog $($Catalog) on $($Server). Found $($foldercount) Folders." } catch{ Write-SbmLogError "Error Connection to Catalog $($Catalog) on $($Server). Details: $($_.Exception.Message)" return } } function Get-SsisFolder{ <# .SYNOPSIS Get SSIS catalog folders #> $sqlStatement = "SELECT [folder_id] FolderId, [name] [FolderName] FROM [$($script:isCatalog)].[catalog].[folders]" $sqlCmd = New-Object System.Data.SqlClient.SqlCommand $sqlStatement , $script:isSqlConnection $sqlReader = $sqlCmd.ExecuteReader() $dataTable = New-Object System.Data.DataTable $dataTable.Load($sqlReader) $dataTable } function Get-SsisProject{ <# .SYNOPSIS Get SSIS catalog projects. .PARAMETER FolderName Optional filter on folder name. .PARAMETER ProjectName Optional filter on project name. #> param( [Parameter(Mandatory=$false)][string]$FolderName, [string]$ProjectName ) $sqlStatement = "SELECT p.[project_id] ProjectId,p.[name] ProjectName,p.[description] ProjectDescription,p.[project_format_version] ProjectFormatversion ,p.[deployed_by_sid] DeployedBySid,p.[deployed_by_name] DeployedByName,p.[last_deployed_time] LastDeployed,p.[created_time] Created ,p.[object_version_lsn] VersionLsn,p.[validation_status] ValidationStatus,p.[last_validation_time] LastValidation ,fl.[folder_id] FolderId, fl.[name] FolderName FROM [$($script:isCatalog)].[catalog].[projects] p JOIN [$($script:isCatalog)].[catalog].[folders] fl ON fl.[folder_id]=p.[folder_id] WHERE 1=1" if ($PSBoundParameters.ContainsKey("FolderName")) { $sqlStatement = $sqlStatement + "AND fl.[name]='$($FolderName)'" } if ($PSBoundParameters.ContainsKey("ProjectName")) { $sqlStatement = $sqlStatement + "AND p.[name]='$($ProjectName)'" } $sqlCmd = New-Object System.Data.SqlClient.SqlCommand $sqlStatement , $script:isSqlConnection $sqlReader = $sqlCmd.ExecuteReader() $dataTable = New-Object System.Data.DataTable $dataTable.Load($sqlReader) $dataTable } function Publish-SsisProject{ <# .SYNOPSIS Deploy SSIS Project to SSIS Catalog. .DESCRIPTION Deploy ispac file to SSIS Catalog folder. .PARAMETER FilePath Full path of ispac file .PARAMETER FolderName Existing Folder name on currently connected server. #> param( [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][Alias('PSPath')][string]$FilePath, [Parameter(Mandatory=$true)][string]$FolderName ) Begin { $importprojectCmd = New-Object System.Data.SqlClient.SqlCommand $importprojectCmd.CommandType = [System.Data.CommandType]::StoredProcedure $importprojectCmd.CommandText = "[$($script:isCatalog)].catalog.deploy_project" $importprojectCmd.Connection = $script:isSqlConnection $importprojectCmd.Parameters.Add("@folder_name", [System.Data.SqlDbType]::NVarChar, 128).Value = $FolderName $importprojectCmd.Parameters.Add("@project_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null $importprojectCmd.Parameters.Add("@project_stream", [System.Data.SqlDbType]::VarBinary, -1) | Out-Null $importprojectCmd.Parameters.Add("@operation_id", [System.Data.SqlDbType]::BigInt).Direction = [System.Data.ParameterDirection]::InputOutput } Process { $fp = Convert-Path $FilePath $ProjectName = [System.IO.Path]::GetFileNameWithoutExtension($fp) #see if the project already exists $prevProject = Get-SsisProject -FolderName $FolderName -ProjectName $ProjectName # Reading ispac file as binary [byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($fp) $importprojectCmd.Parameters["@project_name"].Value = $ProjectName $importprojectCmd.Parameters["@project_stream"].Value = $IspacFile $importprojectCmd.ExecuteNonQuery() if ($prevProject){ Write-SbmLogInformation "Deployed $($ProjectName) to $($FolderName) (REPLACE)" } else { Write-SbmLogInformation "Deployed $($ProjectName) to $($FolderName) (ADD)" } } } function Export-SsisProject { <# .SYNOPSIS Export a SSIS project from the catalog to an ispac file. .PARAMETER ProjectName SSIS catalog project name. .PARAMETER FolderName SSIS catalog folder containing the project. .PARAMETER FolderPath Full path of folder the ispac file is saved to. #> param( [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][string]$ProjectName, [Parameter(Mandatory=$true)][string]$FolderName, [Parameter(Mandatory=$true)][string]$FolderPath ) Begin { $ExportProjectCmd = New-Object System.Data.SqlClient.SqlCommand $ExportProjectCmd.CommandType = [System.Data.CommandType]::StoredProcedure $ExportProjectCmd.CommandText = "[$($script:isCatalog)].catalog.get_project" $ExportProjectCmd.Connection = $script:isSqlConnection $ExportProjectCmd.Parameters.Add("@folder_name", [System.Data.SqlDbType]::NVarChar, 128).Value = $FolderName $ExportProjectCmd.Parameters.Add("@project_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null } Process { $ExportProjectCmd.Parameters["@project_name"].Value = $ProjectName [Byte[]]$fd= $ExportProjectCmd.ExecuteScalar() [System.IO.File]::WriteAllBytes([System.IO.Path]::Combine($FolderPath,$ProjectName + ".ispac") , $fd) } } function Get-SsisParameter{ <# .SYNOPSIS Get SSIS catalog parameter. .PARAMETER FolderName Optional filter on folder name. .PARAMETER ProjectName Optional filter on project name. .PARAMETER ObjectName Optional filter on object name (parameter scope). .PARAMETER WhereValueSet Optional filter only parameters that override the default #> param( [Parameter(Mandatory=$false)][string]$FolderName, [string]$ProjectName, [string]$ObjectName, [switch]$WhereValueSet ) $sqlStatement = "SELECT par.[parameter_id] ParameterId ,par.[project_id] ProjectId ,prj.[name] ProjectName ,par.[object_type] ObjectType ,par.[object_name] ObjectName ,par.[parameter_name] ParameterName ,par.[data_type] DataType ,par.[required] [Required] ,par.[sensitive][Sensitive] ,par.[description] [Description] ,par.[design_default_value] [DesignDefaultValue] ,par.[default_value] [DefaultValue] ,par.[value_type] [ValueType] ,par.[value_set] [ValueSet] ,par.[referenced_variable_name] [ReferencedVariableName] ,par.[validation_status] [ValidationStatus] ,par.[last_validation_time] [LastValidation] ,fl.[name] [FolderName] FROM [$($script:isCatalog)].[catalog].[object_parameters] par JOIN [$($script:isCatalog)].[catalog].[projects] prj ON prj.[project_id]=par.[project_id] JOIN [$($script:isCatalog)].[catalog].[folders] fl ON fl.[folder_id]=prj.[folder_id] WHERE 1=1" if ($PSBoundParameters.ContainsKey("FolderName")){ $sqlStatement = $sqlStatement + "AND fl.[name]='$($FolderName)'" } if ($PSBoundParameters.ContainsKey("ProjectName")){ $sqlStatement = $sqlStatement + "AND prj.[name]='$($ProjectName)'" } if ($PSBoundParameters.ContainsKey("ObjectName")){ $sqlStatement = $sqlStatement + "AND par.[object_name]='$($ObjectName)'" } if ($WhereValueSet) { $sqlStatement = $sqlStatement + "AND par.[value_set]=1" } $sqlCmd = New-Object System.Data.SqlClient.SqlCommand $sqlStatement , $script:isSqlConnection $sqlReader = $sqlCmd.ExecuteReader() $dataTable = New-Object System.Data.DataTable $dataTable.Load($sqlReader) $dataTable } #endregion |