Get-DbaSsisEnvironmentVariable

Want to see the source code for this command? Check out Get-DbaSsisEnvironmentVariable on GitHub.
Want to see the Bill Of Health for this command? Check out Get-DbaSsisEnvironmentVariable.

Synopsis

Retrieves environment variables from SSIS Catalog with decrypted sensitive values

Description

Retrieves all variables from specified SSIS environments stored in the SSISDB catalog database. All sensitive values are automatically decrypted and returned in plaintext for configuration management and troubleshooting purposes.

This function queries the SSISDB database directly using symmetric keys and certificates to decrypt sensitive variable values, bypassing the standard SMO limitations that only return encrypted values. This is essential for SSIS environment configuration audits, parameter validation, and deployment verification.

The function communicates directly with SSISDB database - the SQL Server Integration Services service isn't queried. Each parameter (besides SqlInstance and SqlCredential) acts as a filter to include or exclude specific environments or folders.

Syntax

Get-DbaSsisEnvironmentVariable
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Environment] <Object[]>]
    [[-EnvironmentExclude] <Object[]>]
    [[-Folder] <Object[]>]
    [[-FolderExclude] <Object[]>]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaSsisEnvironmentVariable -SqlInstance localhost -Environment DEV -Folder DWH_ETL

Gets variables of 'DEV' environment located in 'DWH_ETL' folder on 'localhost' Server

Example: 2

PS C:\> Get-DbaSsisEnvironmentVariable -SqlInstance localhost -Environment DEV -Folder DWH_ETL, DEV2, QA

Gets variables of 'DEV' environment(s) located in folders 'DWH_ETL', 'DEV2' and 'QA' on 'localhost' server

Example: 3

PS C:\> Get-DbaSsisEnvironmentVariable -SqlInstance localhost -Environment DEV -FolderExclude DWH_ETL, DEV2, QA

Gets variables of 'DEV' environments located in folders other than 'DWH_ETL', 'DEV2' and 'QA' on 'localhost' server

Example: 4

PS C:\> Get-DbaSsisEnvironmentVariable -SqlInstance localhost -Environment DEV, PROD -Folder DWH_ETL, DEV2, QA

Gets variables of 'DEV' and 'PROD' environment(s) located in folders 'DWH_ETL', 'DEV2' and 'QA' on 'localhost' server

Example: 5

PS C:\> Get-DbaSsisEnvironmentVariable -SqlInstance localhost -EnvironmentExclude DEV, PROD -Folder DWH_ETL, DEV2, QA

Gets variables of environments other than 'DEV' and 'PROD' located in folders 'DWH_ETL', 'DEV2' and 'QA' on 'localhost' server

Example: 6

PS C:\> Get-DbaSsisEnvironmentVariable -SqlInstance localhost -EnvironmentExclude DEV, PROD -FolderExclude DWH_ETL, DEV2, QA

Gets variables of environments other than 'DEV' and 'PROD' located in folders other than 'DWH_ETL', 'DEV2' and 'QA' on 'localhost' server

Example: 7

PS C:\> 'localhost' | Get-DbaSsisEnvironmentVariable -EnvironmentExclude DEV, PROD

Gets all SSIS environments except 'DEV' and 'PROD' from 'localhost' server. The server name comes from pipeline

Example: 8

PS C:\> 'SRV1', 'SRV3' | Get-DbaSsisEnvironmentVariable

Gets all SSIS environments from 'SRV1' and 'SRV3' servers. The server's names come from pipeline

Example: 9

PS C:\> 'SRV1', 'SRV2' | Get-DbaSsisEnvironmentVariable DEV | Out-GridView

Gets all variables from 'DEV' Environment(s) on servers 'SRV1' and 'SRV2' and outputs it as the GridView.
The server names come from the pipeline.

Example: 10

PS C:\> 'localhost' | Get-DbaSsisEnvironmentVariable -EnvironmentExclude DEV, PROD | Select-Object -Property Name, Value | Where-Object {$_.Name -match '^a'} | Out-GridView

Gets all variables from Environments other than 'DEV' and 'PROD' on 'localhost' server,
selects Name and Value properties for variables that names start with letter 'a' and outputs it as the GridView

Required Parameters

-SqlInstance

The target SQL Server instance or instances.
This can be a collection and receive pipeline input to allow the function
to be executed against multiple SQL Server instances.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Environment

Specifies one or more SSIS environment names to retrieve variables from within the SSISDB catalog.
Use this when you need variables from specific environments like 'DEV', 'QA', or 'PROD' rather than all environments in a folder.

-EnvironmentExclude

Excludes specified SSIS environment names from the results when retrieving variables.
Most effective when used without the Environment parameter to get all environments except those specified.
Helpful when you want to audit all non-production environments or exclude specific environments from configuration reviews.

-Folder

Specifies one or more SSISDB catalog folder names that contain the environments you want to query.
Use this to limit your search to specific project folders when you have environments organized by application or team.
If omitted, the function searches all folders in the SSISDB catalog.

-FolderExclude

Excludes specified SSISDB catalog folder names from the search when retrieving environment variables.
Most effective when used without the Folder parameter to search all folders except those specified.
Useful when you want to exclude test folders, archived projects, or specific application folders from your audit.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.