SqlBIManager

1.1.0.0

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. Ignored if the database doesn't already exist.
 
    .PARAMETER RetainRoleMembers
    Retains existing role members on an existing target database. Note: members are only retained if the role exists in the source database.
 
    .PARAMETER RetainDataSources
    Retains existing datasource details such as ConnectionString and ImpersonationMode on an existing target database. Note: details are only retained if the DataSource name exists in the source database.
 
    .EXAMPLE
    Get-ChildItem C:\MySources\Release1 -Filter "*.asdatabase" | Read-AsmlDatabase | Publish-ASDatabase
 
    .EXAMPLE
    Publish-ASDatabase -Database $TestDatabase -ClearValues
 
#>
     [CmdletBinding(SupportsShouldProcess = $true ,ConfirmImpact='Medium',DefaultParameterSetName = 'default')]
     param(
        [parameter(ValueFromPipeline, Mandatory=$true)][PSObject]$Database,
        [switch] $ClearValues,
        [switch] $RetainRoleMembers,
        [switch] $RetainDataSources
     )
     Begin {
        #get list of databases alreday on target server
        $dbs = Get-ASDatabase
     }


     Process {
        $replaceDatabase = $false
        #check if database already exists
        if ($dbs.Name -contains $Database.name)
        {
           $replaceDatabase = $true
        }

        if ($replaceDatabase) {
            if ($ClearValues) {
                Clear-ASDatabase -Name $Database.name
            }
            if ($RetainRoleMembers -or $RetainDataSources) {
                # get existing database meta
                $existingAs = $dbs | Where-Object Name -contains $Database.name
                $existingAsml = ConvertTo-AsmlDatabase $existingAs

                if ($RetainRoleMembers) {
                    #loop through roles
                    foreach ($existingRole in $existingAsml.model.roles) {
                        #find matching role in replacing database
                        $replacingRole = $null
                        $replacingRole = $Database.model.roles | Where-Object name -eq $existingRole.name
                        if (!$replacingRole) {
                            Write-SbmLogInformation "Role $($existingRole.name) is being dropped, members are not retained."
                        }
                        else {
                            #loop through existing role members
                            foreach ($existingRoleMember in $existingRole.members) {
                                $replaceRoleMember = $null
                                $replaceRoleMember =  $replacingRole.members | Where-Object memberId -eq $existingRoleMember.memberId
                                if ($replaceRoleMember) {
                                    Write-SbmLogVerbose "Retaining $($existingRoleMember.memberName) on role $($existingRole.name) (also in source)."
                                }
                                else {
                                    #Does the memberscollection exist?
                                    if ($replacingRole.PSObject.Properties.match("members").Count -eq 0)
                                    {
                                        $_membersColection = @([PSCustomObject]@{"memberId"=$existingRoleMember.memberId;"memberName"=$existingRoleMember.memberName})
                                        $replacingRole | Add-Member "members" $_membersColection
                                    }
                                    else
                                    {
                                        $replacingRole.Members+= [PSCustomObject]@{"memberId"=$existingRoleMember.memberId;"memberName"=$existingRoleMember.memberName}
                                    }
                                    Write-SbmLogInformation "Retaining $($existingRoleMember.memberName) on role $($existingRole.name)."
                                }
                            }
                        }
                    }
                }

                if ($RetainDataSources) {
                    #loop through data sources
                    foreach ($existingDataSource in $existingAsml.model.dataSources) {
                        #find matching DataSource in replacing database
                        $replacingDatasource = $null
                        $replacingDatasource = $Database.model.dataSources | Where-Object name -eq $existingDataSource.name
                        if (!$replacingDatasource) {
                            Write-SbmLogInformation "DataSource $($existingDataSource.name) is being dropped, details are not retained."
                        }
                        else {
                            $Database.model.dataSources[$Database.model.dataSources.IndexOf( $replacingDatasource)] = $existingDataSource.PSObject.Copy()
                            Write-SbmLogInformation "Retaining DataSource $($existingDataSource.name) details."
                        }
                    }
                }
            }

        }

        $createorReplace = [PSCustomObject]@{ "createOrReplace"= [PSCustomObject]@{ 
        "object"= [PSCustomObject]@{ "database" = $Database.name};
        "database" = $Database  
        }}

        $ScriptContent = ConvertTo-Json $createorReplace -Depth 25
        
        if ($PSCmdlet.ShouldProcess("Database $($Database.name).",  "Create or replace")){
            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 "*.asdatabase" | 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 database 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=$false)][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) (Database $($Database.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) (Database $($Database.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
     }
} 


<#
.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 Filter
Optional Filter expression evaluated for every database/role. If filter evaluates to false, member is not removed. Tip: filter on $input.Role.name, $input.Member.memberName, $input.Database.name.
.PARAMETER FilterArgumentList
Optional arguments evaluated in the filter.
#>
function Clear-AsmlRoleMember
{
     param(
        [parameter(ValueFromPipeline,Mandatory=$true)][PSObject]$Database,
        [Parameter(Mandatory=$false)][string] $RoleName,
        [Parameter(Mandatory=$false)][PSObject] $Member,
        [ScriptBlock] $Filter,
        [Object[]] $FilterArgumentList
     )

     Process {
        $roles = $Database.model.roles
        if ($PSBoundParameters.ContainsKey("RoleName")) {
            $roles = $roles | Where-Object name -eq $RoleName
        }
        Foreach ($role in $roles)
        {
            $replacedMembers = @()
            foreach ($roleMember in $role.members) {
                $filterResult = $true
                if ($PSBoundParameters.ContainsKey("Filter")) {
                    $filterInput = [PSCustomObject] @{
                        "Database"=$Database
                        "Role"=$role
                        "Member"=$roleMember
                    }
                    $filterResult = Invoke-Command -ScriptBlock $Filter -ArgumentList $FilterArgumentList -InputObject $filterInput
                }

                if ($filterResult -eq $true) {
                    if  ($PSBoundParameters.ContainsKey("Member")) {
                        if ($roleMember.memberId -ne $Member.memberId) {
                             $filterResult = $false
                        }
                    }
                }

                if ($filterResult -eq $true) {
                    Write-SbmLogInformation "Remove member $($roleMember.memberName) from role $($role.name) (Database $($Database.name))."
                }
                else {
                    $replacedMembers += $roleMember
                }
            }
            if ($role.PSObject.Properties.Match("members").Count -gt 0){ 
                $role.members = $replacedMembers
            }
        }
        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)\$($ADObject.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 Catalog, Folder, Project
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)
    }
}
#endregion

#region SSIS Parameter, Environment
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]
          ,IIF(par.[value_set]='FALSE', par.[design_default_value]
            , IIF(par.[value_type]='R',par.[referenced_variable_name],par.[default_value])) [ParameterValue]
        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
}

function Set-SsisParameter {
<#
.SYNOPSIS
Set SSIS catalog parameter value.
.DESCRIPTION
Sets the value of aproject or package parameter to a environment reference value or a literal value. Can take input from Get-SsisParameter output.
.PARAMETER ObjectType
20 indicates project parameter, 30 indicates package parameter. Defaults to 20.
.PARAMETER FolderName
Catalog folder name
.PARAMETER ProjectName
Catalog project name.
.PARAMETER ParameterName
Project or package parameter name, depending on ObjectType.
.PARAMETER ParameterValue
Project or package parameter literal value or reference variable name.
.PARAMETER ObjectName
Package name, required if ObjectType is 30.
.PARAMETER ValueType
V indicates literal value, R indicates referenced environment variable. Defaults to V.
#>
    [CmdletBinding(SupportsShouldProcess = $true,ConfirmImpact='Medium',DefaultParameterSetName = 'default')]
    param(
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$false)][int] $ObjectType = 20,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][string] $FolderName,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][string] $ProjectName,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][string] $ParameterName,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][object] $ParameterValue,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$false)][string] $ObjectName,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$false)][string] $ValueType = "V"
    )
    Begin {
        $sqlCmd = New-Object System.Data.SqlClient.SqlCommand 
        $sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $sqlCmd.CommandText = "[$($script:isCatalog)].catalog.set_object_parameter_value"
        $sqlCmd.Connection = $script:isSqlConnection
        $sqlCmd.Parameters.Add("@object_type", [System.Data.SqlDbType]::SmallInt) | Out-Null
        $sqlCmd.Parameters.Add("@folder_name", [System.Data.SqlDbType]::NVarChar, 128)| Out-Null
        $sqlCmd.Parameters.Add("@project_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $sqlCmd.Parameters.Add("@parameter_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $sqlCmd.Parameters.Add("@parameter_value", [System.Data.SqlDbType]::Variant) | Out-Null
        $sqlCmd.Parameters.Add("@object_name", [System.Data.SqlDbType]::NVarChar, 260) | Out-Null
        $sqlCmd.Parameters.Add("@value_type", [System.Data.SqlDbType]::Char, 1) | Out-Null
    }

    Process {
        $msg = "Set-SsisParameter Folder:$($FolderName) Project:$($ProjectName) Parameter:$($ParameterName) Value:$($ParameterValue)"
        if ($PSCmdlet.ShouldProcess($msg)){
            $sqlCmd.Parameters["@object_type"].Value = $ObjectType 
            $sqlCmd.Parameters["@folder_name"].Value = $FolderName
            $sqlCmd.Parameters["@project_name"].Value = $ProjectName
            $sqlCmd.Parameters["@parameter_name"].Value = $ParameterName
            $sqlCmd.Parameters["@parameter_value"].Value = $ParameterValue
            $sqlCmd.Parameters["@object_name"].Value = $ObjectName
            $sqlCmd.Parameters["@value_type"].Value = $ValueType
            [int]$exrst= $sqlCmd.ExecuteNonQuery()
            Write-SbmLogInformation -Message "$($msg) Result:$($exrst)"
        }
    }

}

function Clear-SsisParameter {
<#
.SYNOPSIS
Clear SSIS catalog parameter value.
.DESCRIPTION
Resets the value of a project or package parameter to the design default. Can take input from Get-SsisParameter output.
.PARAMETER ObjectType
20 indicates project parameter, 30 indicates package parameter. Defaults to 20.
.PARAMETER FolderName
Catalog folder name
.PARAMETER ProjectName
Catalog project name.
.PARAMETER ParameterName
Project or package parameter name, depending on ObjectType.
.PARAMETER ObjectName
Package name, required if ObjectType is 30.
#>
    [CmdletBinding(SupportsShouldProcess = $true,ConfirmImpact='Medium',DefaultParameterSetName = 'default')]
    param(
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$false)][int] $ObjectType = 20,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][string] $FolderName,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][string] $ProjectName,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][string] $ParameterName,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$false)][string] $ObjectName
    )
    Begin {
        $sqlCmd = New-Object System.Data.SqlClient.SqlCommand 
        $sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $sqlCmd.CommandText = "[$($script:isCatalog)].catalog.clear_object_parameter_value"
        $sqlCmd.Connection = $script:isSqlConnection
        $sqlCmd.Parameters.Add("@object_type", [System.Data.SqlDbType]::SmallInt) | Out-Null
        $sqlCmd.Parameters.Add("@folder_name", [System.Data.SqlDbType]::NVarChar, 128)| Out-Null
        $sqlCmd.Parameters.Add("@project_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $sqlCmd.Parameters.Add("@parameter_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $sqlCmd.Parameters.Add("@object_name", [System.Data.SqlDbType]::NVarChar, 260) | Out-Null
    }

    Process {
        $msg = "Clear-SsisParameter Folder:$($FolderName) Project:$($ProjectName) Parameter:$($ParameterName) Value:$($ParameterValue)"
        if ($PSCmdlet.ShouldProcess($msg)){
            $sqlCmd.Parameters["@object_type"].Value = $ObjectType 
            $sqlCmd.Parameters["@folder_name"].Value = $FolderName
            $sqlCmd.Parameters["@project_name"].Value = $ProjectName
            $sqlCmd.Parameters["@parameter_name"].Value = $ParameterName
            $sqlCmd.Parameters["@object_name"].Value = $ObjectName
            [int]$exrst= $sqlCmd.ExecuteNonQuery()
            Write-SbmLogInformation -Message "$($msg) Result:$($exrst)"
        }
    }

}
function Get-SsisEnvironment{
<#
.SYNOPSIS
Get SSIS environment variables.
.PARAMETER FolderName
Optional filter on folder name.
.PARAMETER EnvironmentName
Optional filter on project name.
#>
     param(
        [Parameter(Mandatory=$false)][string]$FolderName,
        [string]$EnvironmentName
     )
     $sqlStatement = "SELECT env.[environment_id] [EnvironmentId]
      ,env.[name] [EnvironmentName]
      ,env.[description] [EnvironmentDescription]
      ,env.[folder_id] [FolderId]
      ,fl.[name] [FolderName]
        FROM [$($script:isCatalog)].[catalog].[environments] env
        JOIN [$($script:isCatalog)].[catalog].[folders] fl ON fl.[folder_id]=env.[folder_id]
        WHERE 1=1"
     if ($PSBoundParameters.ContainsKey("FolderName")){
        $sqlStatement = $sqlStatement + "AND fl.[name]='$($FolderName)'"
     }
     if ($PSBoundParameters.ContainsKey("EnvironmentName")){
        $sqlStatement = $sqlStatement + "AND env.[name]='$($EnvironmentName)'"
     }

     $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-SsisEnvironmentVariable{
<#
.SYNOPSIS
Get SSIS environment variables.
.PARAMETER FolderName
Optional filter on folder name.
.PARAMETER EnvironmentName
Optional filter on project name.
.PARAMETER VariableName
Optional filter on environment ariablename.
#>
     param(
        [Parameter(Mandatory=$false)][string]$FolderName,
        [string]$EnvironmentName,
        [string]$VariableName
     )
     $sqlStatement = "SELECT ev.[variable_id] [VariableId]
      ,ev.[name] [VariableName]
      ,ev.[description] [VariableDescription]
      ,ev.[type] [VariableType]
      ,ev.[sensitive][VariableSensitive]
      ,ev.[value] [VariableValue]
      ,ev.[environment_id] [EnvironmentId]
      ,env.[name] [EnvironmentName]
      ,env.[folder_id] [FolderId]
      ,fl.[name] [FolderName]
        FROM [$($script:isCatalog)].[catalog].[environment_variables] ev
        JOIN [$($script:isCatalog)].[catalog].[environments] env ON env.[environment_id]=ev.[environment_id]
        JOIN [$($script:isCatalog)].[catalog].[folders] fl ON fl.[folder_id]=env.[folder_id]
        WHERE 1=1"
     if ($PSBoundParameters.ContainsKey("FolderName")){
        $sqlStatement = $sqlStatement + "AND fl.[name]='$($FolderName)'"
     }
     if ($PSBoundParameters.ContainsKey("EnvironmentName")){
        $sqlStatement = $sqlStatement + "AND env.[name]='$($EnvironmentName)'"
     }
     if ($PSBoundParameters.ContainsKey("VariableName")){
        $sqlStatement = $sqlStatement + "AND ev.[name]='$($VariableName)'"
     }

     $sqlCmd = New-Object System.Data.SqlClient.SqlCommand  $sqlStatement , $script:isSqlConnection
     $sqlReader =  $sqlCmd.ExecuteReader()

     $dataTable = New-Object System.Data.DataTable
     $dataTable.Load($sqlReader)

     $dataTable
}

function Set-SsisEnvironmentVariableValue {
<#
.SYNOPSIS
Set SSIS catalog environment variable value.
.DESCRIPTION
Sets the value of a environment variable. Can take input from Get-SsisEnvironmentVariable output.
.PARAMETER FolderName
Catalog folder name
.PARAMETER ProjectName
Catalog project name.
.PARAMETER EnvironmentName
Environment VariableName
.PARAMETER ParameterValue
Environment variable name.
.PARAMETER VariableValue
Environment variable value.
#>
    [CmdletBinding(SupportsShouldProcess = $true,ConfirmImpact='Medium',DefaultParameterSetName = 'default')]
    param(
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][string] $FolderName,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][string] $EnvironmentName,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][string] $VariableName,
        [parameter(ValueFromPipelineByPropertyName, Mandatory=$true)][object] $VariableValue
    )
    Begin {
        $sqlCmd = New-Object System.Data.SqlClient.SqlCommand 
        $sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $sqlCmd.CommandText = "[$($script:isCatalog)].catalog.set_environment_variable_value"
        $sqlCmd.Connection = $script:isSqlConnection
        $sqlCmd.Parameters.Add("@folder_name", [System.Data.SqlDbType]::NVarChar, 128)| Out-Null
        $sqlCmd.Parameters.Add("@environment_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $sqlCmd.Parameters.Add("@variable_name", [System.Data.SqlDbType]::NVarChar, 128) | Out-Null
        $sqlCmd.Parameters.Add("@value", [System.Data.SqlDbType]::Variant) | Out-Null
    }

    Process {
        $msg = "Set-SsisEnvironmentVariable Folder:$($FolderName) Environment:$($EnvironmentName) Variable:$($VariableName) Value:$($VariableValue)"
        if ($PSCmdlet.ShouldProcess($msg)){
            $sqlCmd.Parameters["@folder_name"].Value = $FolderName
            $sqlCmd.Parameters["@environment_name"].Value = $EnvironmentName
            $sqlCmd.Parameters["@variable_name"].Value = $VariableName
            $sqlCmd.Parameters["@value"].Value = $VariableValue
            [int]$exrst= $sqlCmd.ExecuteNonQuery()
            Write-SbmLogInformation -Message "$($msg) Result:$($exrst)"
        }
    }

}

#endregion