--- title: "Get-DbaSsisEnvironmentVariable" description: "Retrieves environment variables from SSIS Catalog with decrypted sensitive values" url: "https://dbatools.io/Get-DbaSsisEnvironmentVariable/" availability: "Windows only" tags: ["SSIS", "SSISDB", "Variable"] author: "Bartosz Ratajczyk (@b_ratajczyk)" source: "https://github.com/dataplat/dbatools/blob/master/public/Get-DbaSsisEnvironmentVariable.ps1" last_updated: "2024-01-01" --- # 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 ```powershell Get-DbaSsisEnvironmentVariable [-SqlInstance] [[-SqlCredential] ] [[-Environment] ] [[-EnvironmentExclude] ] [[-Folder] ] [[-FolderExclude] ] [-EnableException] [] ``` ## Examples ### Example 1: Gets variables of 'DEV' environment located in 'DWH_ETL' folder on 'localhost' Server ```powershell PS C:\> Get-DbaSsisEnvironmentVariable -SqlInstance localhost -Environment DEV -Folder DWH_ETL ``` ### Example 2: Gets variables of 'DEV' environment(s) located in folders 'DWH_ETL', 'DEV2' and 'QA' on 'localhost' server ```powershell PS C:\> Get-DbaSsisEnvironmentVariable -SqlInstance localhost -Environment DEV -Folder DWH_ETL, DEV2, QA ``` ### Example 3: Gets variables of 'DEV' environments located in folders other than 'DWH_ETL', 'DEV2' and 'QA' on 'localhost'... ```powershell 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: Gets variables of 'DEV' and 'PROD' environment(s) located in folders 'DWH_ETL', 'DEV2' and 'QA' on... ```powershell 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: Gets variables of environments other than 'DEV' and 'PROD' located in folders 'DWH_ETL', 'DEV2' and 'QA' on... ```powershell 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: Gets variables of environments other than 'DEV' and 'PROD' located in folders other than 'DWH_ETL', 'DEV2'... ```powershell 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: Gets all SSIS environments except 'DEV' and 'PROD' from 'localhost' server ```powershell 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: Gets all SSIS environments from 'SRV1' and 'SRV3' servers ```powershell PS C:\> 'SRV1', 'SRV3' | Get-DbaSsisEnvironmentVariable ``` Gets all SSIS environments from 'SRV1' and 'SRV3' servers. The server's names come from pipeline ### Example 9: Gets all variables from 'DEV' Environment(s) on servers 'SRV1' and 'SRV2' and outputs it as the GridView ```powershell 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: Gets all variables from Environments other than 'DEV' and 'PROD' on 'localhost' server, selects Name and... ```powershell 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. | Property | Value | | --- | --- | | Alias | | | Required | True | | Pipeline | true (ByValue) | | Default Value | | ### 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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | | ##### -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. | Property | Value | | --- | --- | | Alias | | | Required | False | | Pipeline | false | | Default Value | False | ## Outputs **PSCustomObject** Returns one object per SSIS environment variable found in the filtered environments and folders. Sensitive variable values are automatically decrypted and returned in plaintext. **Properties:** - ComputerName: The name of the computer hosting the SQL Server instance - InstanceName: The SQL Server instance name - SqlInstance: The full SQL Server instance name (computer\instance) - Folder: The SSISDB catalog folder name containing the environment - Environment: The name of the SSIS environment containing the variable - Id: Unique identifier for the variable within the environment - Name: The logical name of the environment variable - Description: Text description of the variable's purpose - Type: The type of variable (typically 'String', 'Int32', etc.) - IsSensitive: Boolean indicating if the variable value is sensitive (encrypted in SSISDB) - BaseDataType: The base data type used for the variable value storage - Value: The actual variable value; automatically decrypted if sensitive, plaintext if not --- Part of [dbatools](https://dbatools.io/), a free and open source PowerShell module for SQL Server administration. Full command index: https://dbatools.io/commands/