====== SSIS Proxy & Verbose Logging Cheat Sheet ======

=== 🛠️ Running SSIS Job Steps as a Proxy ===

To run an SSIS package as a different user (e.g. ''domain\administrator''), follow these steps:

== 1. Create Credential ==
<code sql>
USE [master];
CREATE CREDENTIAL [ssis_cred_admin]
WITH IDENTITY = N'domain\administrator',
     SECRET = N'YourDomainPasswordHere';
</code>

== 2. Create Proxy Account ==
<code sql>
USE [msdb];
EXEC sp_add_proxy 
    @proxy_name = N'SSIS Proxy Admin',
    @credential_name = N'ssis_cred_admin';
</code>

== 3. Grant Access to SSIS Subsystem ==
<code sql>
EXEC sp_grant_proxy_to_subsystem 
    @proxy_name = N'SSIS Proxy Admin',
    @subsystem_id = 11;  -- SSIS
</code>

== 4. (Optional) Grant Proxy to Specific Login ==
<code sql>
EXEC sp_grant_login_to_proxy 
    @login_name = N'MySqlAgentJobLogin', 
    @proxy_name = N'SSIS Proxy Admin';
</code>

== 5. Configure Job Step ==
  * Type: **SQL Server Integration Services Package**
  * Run as: **SSIS Proxy Admin**

<WRAP center round important 60%>
You’re not creating a new user — you're just wrapping sa in a credential and proxy. A credential is a way to associate a username and password with a proxy in SQL Server Agent. You’re essentially telling SQL Server, “Here’s how to impersonate 'domain\administrator' when needed.”
</WRAP>

---

=== 🔍 Enabling Verbose Logging for SSIS ===

== 1. Enable Logging in SSIS Package ==
  * Open SSIS package in Visual Studio/SSDT
  * Go to **SSIS → Logging...**
  * Choose provider (Text file, SQL Server, SSISDB)
  * Enable events: 
    * ''OnError'', ''OnWarning'', ''OnPre/PostExecute'', ''OnTaskFailed''

== 2. Enable Output File in SQL Agent Job Step ==
  * Job Properties → Steps → [Your Step] → Edit
  * Go to **Advanced tab**
  * Set Output file:
<code>
C:\SSISLogs\MyJobOutput.txt
</code>

== 3. Use DTEXEC Logging Parameters (if CmdExec) ==
<code>
dtexec /FILE "path_to_package.dtsx" /DECRYPT password /REPORTING E D I > "C:\SSISLogs\ssis_debug.txt"
</code>

=== /REPORTING Flags ===
| Flag | Meaning              |
|------|----------------------|
| N    | Errors only          |
| E    | Errors + events      |
| D    | All events           |
| I    | SQL statements       |
| P    | Progress             |
| V    | Version info         |
| C    | Custom events        |

---

=== 📊 If Package is Deployed to SSISDB ===

  * Go to: **SSMS → SSISDB → [Project] → [Package]**
  * Right-click → **Reports → All Executions**
  * Click execution → **Messages** tab

---

=== 🧪 Troubleshooting Tips ===

  * Error:
    <code>
    NT SERVICE\SQLSERVERAGENT does not have access to 'My_Database'
    </code>
    → The job step is likely not using the proxy correctly.

  * Test with ''CmdExec'' job step and ''dtexec'' for clearer logs.
  * Check **Windows Event Viewer** (Application log).
  * Check **SQL Server Agent job history** and output files.
  * Use ''whoami'' inside SSIS Script Task to verify run context.

---

=== 🧼 Cleanup (Optional) ===
<code sql>
DROP CREDENTIAL [ssis_cred_admin];
EXEC sp_delete_proxy @proxy_name = N'SSIS Proxy Admin';
</code>