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

USE [master];
CREATE CREDENTIAL [ssis_cred_admin]
WITH IDENTITY = N'domain\administrator',
     SECRET = N'YourDomainPasswordHere';

2. Create Proxy Account

USE [msdb];
EXEC sp_add_proxy 
    @proxy_name = N'SSIS Proxy Admin',
    @credential_name = N'ssis_cred_admin';

3. Grant Access to SSIS Subsystem

EXEC sp_grant_proxy_to_subsystem 
    @proxy_name = N'SSIS Proxy Admin',
    @subsystem_id = 11;  -- SSIS

4. (Optional) Grant Proxy to Specific Login

EXEC sp_grant_login_to_proxy 
    @login_name = N'MySqlAgentJobLogin', 
    @proxy_name = N'SSIS Proxy Admin';

5. Configure Job Step

Type: SQL Server Integration Services Package
Run as: SSIS Proxy Admin

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.”

—

🔍 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:

C:\SSISLogs\MyJobOutput.txt

3. Use DTEXEC Logging Parameters (if CmdExec)

dtexec /FILE "path_to_package.dtsx" /DECRYPT password /REPORTING E D I > "C:\SSISLogs\ssis_debug.txt"

/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:

    NT SERVICE\SQLSERVERAGENT does not have access to 'My_Database'

  → 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)

DROP CREDENTIAL [ssis_cred_admin];
EXEC sp_delete_proxy @proxy_name = N'SSIS Proxy Admin';