# 機能設計書 530-Get-DbaSsisEnvironmentVariable

## 概要

本ドキュメントは、SSIS 環境変数を復号化して取得する `Get-DbaSsisEnvironmentVariable` コマンドの機能設計書です。

### 本機能の処理概要

SSISDB カタログに保存されている SSIS 環境変数を取得し、Sensitive（機密）値を自動的に復号化して平文で返す PowerShell コマンドです。T-SQL と対称キーを使用して直接 SSISDB データベースを照会します。

**業務上の目的・背景**：SSIS 環境変数には、接続文字列、パスワード、API キーなどの重要な設定が含まれています。SMO の標準的な方法では Sensitive 値は暗号化されたままで取得できません。本コマンドは、構成管理、監査、デプロイメント検証などの目的で、すべての環境変数（Sensitive 値を含む）を平文で取得する手段を提供します。

**機能の利用シーン**：
- SSIS 環境設定の監査とドキュメント化
- デプロイメント前後の設定検証
- 環境間の設定比較
- トラブルシューティング時の設定確認
- コンプライアンスレポート生成

**主要な処理内容**：
1. SQL Server インスタンスへの接続
2. SSISDB カタログとフォルダーの取得
3. フォルダー/環境フィルターの適用
4. 対称キーを使用した Sensitive 値の復号化
5. 環境変数情報の出力

**関連システム・外部連携**：SQL Server Integration Services、SSISDB データベース、T-SQL 暗号化関数

**権限による制御**：SSISDB データベースへの読み取りアクセスと、対称キーへのアクセス権が必要です。

## 関連画面

| 画面No | 画面名 | 関連種別 | 関連する操作・処理 |
|--------|--------|----------|------------------|
| - | SQL Server Management Studio - Integration Services Catalogs | 参照画面 | 環境変数の確認と同等（ただし SMO では Sensitive 値は非表示） |

## 機能種別

情報取得 / 監査

## 入力仕様

### 入力パラメータ

| パラメータ名 | 型 | 必須 | 説明 | バリデーション |
|-------------|-----|-----|------|---------------|
| SqlInstance | DbaInstanceParameter[] | Yes | 対象の SQL Server インスタンス | SQL Server 2012 以上 |
| SqlCredential | PSCredential | No | 代替認証情報 | - |
| Environment | object[] | No | 取得する環境名 | - |
| EnvironmentExclude | object[] | No | 除外する環境名 | - |
| Folder | object[] | No | 対象のフォルダー名 | - |
| FolderExclude | object[] | No | 除外するフォルダー名 | - |
| EnableException | switch | No | 例外をスローする | - |

### 入力データソース

- SSISDB カタログ（IntegrationServices オブジェクト）
- internal.environment_variables テーブル

## 出力仕様

### 出力データ

| 項目名 | 型 | 説明 |
|--------|-----|------|
| ComputerName | string | SQL Server をホストするコンピュータ名 |
| InstanceName | string | SQL Server インスタンス名 |
| SqlInstance | string | 完全な SQL Server インスタンス名 |
| Folder | string | SSISDB フォルダー名 |
| Environment | string | SSIS 環境名 |
| Id | int | 変数 ID |
| Name | string | 変数名 |
| Description | string | 変数の説明 |
| Type | string | 変数タイプ |
| IsSensitive | bool | Sensitive フラグ |
| BaseDataType | string | 基本データタイプ |
| Value | string | 変数値（Sensitive の場合は復号化済み） |

### 出力先

- PowerShell パイプライン出力（PSCustomObject）

## 処理フロー

### 処理シーケンス

```
1. プラットフォームチェック
   └─ PowerShell Core（Linux/macOS）では実行不可
2. SQL Server インスタンスへの接続
   └─ Connect-DbaInstance（MinimumVersion 11）
3. IntegrationServices オブジェクト作成
   └─ SSISDB カタログ取得
4. フォルダーフィルタリング
   └─ -Folder 指定: 指定フォルダーのみ
   └─ -FolderExclude 指定: 除外適用
   └─ 指定なし: 全フォルダー
5. 各フォルダーの処理ループ
   └─ 環境フィルタリング
      └─ -Environment 指定: 指定環境のみ
      └─ -EnvironmentExclude 指定: 除外適用
      └─ 指定なし: 全環境
6. 各環境の変数取得
   └─ 対称キーオープン（OPEN SYMMETRIC KEY）
   └─ internal.environment_variables クエリ
   └─ DECRYPTBYKEY で Sensitive 値復号化
   └─ 対称キークローズ（CLOSE SYMMETRIC KEY）
7. 結果出力
   └─ Sensitive の場合: 復号化値を使用
   └─ 非 Sensitive の場合: そのまま使用
```

### フローチャート

```mermaid
flowchart TD
    A[開始] --> B{PS Core?}
    B -->|Yes| C[エラー終了]
    B -->|No| D[SQL Server 接続]
    D --> E{接続成功?}
    E -->|No| F[エラー終了]
    E -->|Yes| G[IntegrationServices 作成]
    G --> H[SSISDB カタログ取得]
    H --> I[フォルダーフィルタ適用]
    I --> J{フォルダーあり?}
    J -->|No| K[警告・スキップ]
    J -->|Yes| L[各フォルダーループ]
    L --> M[環境フィルタ適用]
    M --> N{環境あり?}
    N -->|No| O[警告・スキップ]
    N -->|Yes| P[各環境ループ]
    P --> Q[対称キーオープン]
    Q --> R[変数クエリ実行]
    R --> S[対称キークローズ]
    S --> T[各変数をループ]
    T --> U{Sensitive?}
    U -->|Yes| V[復号化値を使用]
    U -->|No| W[元の値を使用]
    V --> X[PSCustomObject 出力]
    W --> X
    X --> Y{次の変数?}
    Y -->|Yes| T
    Y -->|No| Z{次の環境?}
    Z -->|Yes| P
    Z -->|No| AA{次のフォルダー?}
    AA -->|Yes| L
    AA -->|No| AB[終了]
```

## ビジネスルール

### 業務ルール

| ルールNo | ルール名 | 内容 | 適用条件 |
|---------|---------|------|---------|
| BR-001 | プラットフォーム制限 | Linux/macOS では実行不可 | 常時 |
| BR-002 | 最小バージョン | SQL Server 2012 以上が必要 | 常時 |
| BR-003 | 復号化ロジック | Sensitive=true の場合のみ復号化値を使用 | 変数取得時 |
| BR-004 | データタイプ変換 | base_data_type に応じて DECRYPTBYKEY 結果を変換 | 復号化時 |
| BR-005 | 除外フィルター優先 | Exclude パラメータは Include パラメータより後に適用 | フィルタリング時 |

### 計算ロジック

復号化ロジック:
```sql
CASE base_data_type
    WHEN 'nvarchar' THEN CONVERT(NVARCHAR(MAX), DECRYPTBYKEY(sensitive_value))
    WHEN 'bit' THEN CONVERT(NVARCHAR(MAX), CONVERT(BIT, DECRYPTBYKEY(sensitive_value)))
    WHEN 'datetime' THEN CONVERT(NVARCHAR(MAX), CONVERT(DATETIME2(0), DECRYPTBYKEY(sensitive_value)))
    WHEN 'single' THEN CONVERT(NVARCHAR(MAX), CONVERT(DECIMAL(38, 18), DECRYPTBYKEY(sensitive_value)))
    WHEN 'float' THEN CONVERT(NVARCHAR(MAX), CONVERT(DECIMAL(38, 18), DECRYPTBYKEY(sensitive_value)))
    WHEN 'decimal' THEN CONVERT(NVARCHAR(MAX), CONVERT(DECIMAL(38, 18), DECRYPTBYKEY(sensitive_value)))
    WHEN 'tinyint' THEN CONVERT(NVARCHAR(MAX), CONVERT(TINYINT, DECRYPTBYKEY(sensitive_value)))
    WHEN 'smallint' THEN CONVERT(NVARCHAR(MAX), CONVERT(SMALLINT, DECRYPTBYKEY(sensitive_value)))
    WHEN 'int' THEN CONVERT(NVARCHAR(MAX), CONVERT(INT, DECRYPTBYKEY(sensitive_value)))
    WHEN 'bigint' THEN CONVERT(NVARCHAR(MAX), CONVERT(BIGINT, DECRYPTBYKEY(sensitive_value)))
END
```

## データベース操作仕様

### 操作別データベース影響一覧

| 操作 | 対象テーブル | 操作種別 | 概要 |
|-----|-------------|---------|------|
| 変数取得 | SSISDB.internal.environment_variables | SELECT | 環境変数取得 |
| 復号化 | 対称キー | OPEN/CLOSE | MS_Enckey_Env_* キー使用 |

### テーブル別操作詳細

#### SSISDB.internal.environment_variables

| 操作 | 項目（カラム名） | 更新値・取得条件 | 備考 |
|-----|-----------------|-----------------|------|
| SELECT | variable_id, name, description, type, sensitive, value, sensitive_value, base_data_type | environment_id 指定 | DECRYPTBYKEY で復号化 |

## エラー処理

### エラーケース一覧

| エラーコード | エラー種別 | 発生条件 | 対処方法 |
|------------|----------|---------|---------|
| - | プラットフォームエラー | PowerShell Core で実行 | Windows PowerShell を使用 |
| - | バージョンエラー | SQL Server 2012 未満 | SQL Server 2012 以上を使用 |
| - | 接続エラー | IntegrationServices 作成失敗 | SSIS と SSISDB を確認 |
| - | フィルターエラー | フォルダー/環境が空 | フィルター条件を確認 |

### リトライ仕様

リトライなし（読み取り専用操作）

## トランザクション仕様

読み取り専用操作のためトランザクションなし
対称キーは明示的にオープン・クローズ

## パフォーマンス要件

- 環境変数数に比例した処理時間
- 復号化処理によるオーバーヘッドあり

## セキュリティ考慮事項

- Sensitive 値が平文で返される点に注意
- 対称キーへのアクセス権が必要
- 結果の取り扱いには十分な注意が必要

## 備考

- SMO では Sensitive 値が取得できないため、直接 T-SQL を使用
- 各環境ごとに専用の対称キーが存在
- PowerShell Core では実行不可（Windows 専用）

---

## コードリーディングガイド

本機能を理解するために参照すべきファイルと、推奨する読み解き順序を以下に示す。

### 推奨読解順序

#### Step 1: SQL クエリを理解する

復号化を行う T-SQL が本関数の核心部分です。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 1-1 | Get-DbaSsisEnvironmentVariable.ps1 | `public/Get-DbaSsisEnvironmentVariable.ps1` | 復号化 SQL（215-247行目） |

**読解のコツ**:
- OPEN/CLOSE SYMMETRIC KEY で対称キーを開閉
- CROSS APPLY で復号化結果を計算
- base_data_type に応じた型変換

#### Step 2: エントリーポイントを理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 2-1 | Get-DbaSsisEnvironmentVariable.ps1 | `public/Get-DbaSsisEnvironmentVariable.ps1` | 関数定義とパラメータ |

**主要処理フロー**:
1. **138-148行目**: パラメータ定義
2. **149-278行目**: process ブロック

#### Step 3: フィルタリングロジックを理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 3-1 | Get-DbaSsisEnvironmentVariable.ps1 | `public/Get-DbaSsisEnvironmentVariable.ps1` | フィルタリング処理 |

**主要処理フロー**:
- **171-181行目**: フォルダーフィルタリング
- **188-197行目**: 環境フィルタリング

#### Step 4: 結果出力を理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 4-1 | Get-DbaSsisEnvironmentVariable.ps1 | `public/Get-DbaSsisEnvironmentVariable.ps1` | 結果処理 |

**主要処理フロー**:
- **251-271行目**: 各変数のループ処理と出力

### プログラム呼び出し階層図

```
Get-DbaSsisEnvironmentVariable
    │
    ├─ Connect-DbaInstance
    │      └─ SQL Server SMO 接続（MinimumVersion 11）
    │
    ├─ New-Object IntegrationServices
    │      └─ SSIS 管理オブジェクト作成
    │
    ├─ $catalog.Folders
    │      └─ フォルダーコレクション取得
    │
    ├─ $folder.Environments
    │      └─ 環境コレクション取得
    │
    └─ $server.Query($sql, "SSISDB")
           └─ 復号化クエリ実行
```

### データフロー図

```
[入力]               [処理]                    [出力]

SqlInstance ───▶ Connect-DbaInstance ───▶ Server Object
                         │
Folder/Env ────▶ Filter Apply ─────────▶ Target Objects
                         │
                  OPEN SYMMETRIC KEY ───▶ Key Access
                         │
                  Query Variables ──────▶ Raw Data
                         │
                  DECRYPTBYKEY ─────────▶ Decrypted Values
                         │
                  CLOSE SYMMETRIC KEY ──▶ Key Closed
                         │
                  PSCustomObject ───────▶ Pipeline Output
```

### 関連ファイル一覧

| ファイル | パス | 種別 | 役割 |
|---------|------|------|------|
| Get-DbaSsisEnvironmentVariable.ps1 | `public/Get-DbaSsisEnvironmentVariable.ps1` | ソース | メイン関数定義 |
| Connect-DbaInstance.ps1 | `public/Connect-DbaInstance.ps1` | ソース | SQL Server 接続 |
