Ssis-661

If you must stay with a non‑Unicode destination, the following C# snippet inside a Script Component (Transformation) safely converts while logging dropped characters:

public override void Input0_ProcessInputRow(Input0Buffer Row)
if (!Row.UnicodeCol_IsNull)
// Source Unicode string
        string src = Row.UnicodeCol;
// Target encoding (1252 = Latin-1)
        Encoding targetEnc = Encoding.GetEncoding(1252, EncoderFallback.ReplacementFallback,
                                                       DecoderFallback.ExceptionFallback);
try
// Convert, replacing unrepresentable chars with '?'
            byte[] bytes = Encoding.Convert(Encoding.Unicode, targetEnc, Encoding.Unicode.GetBytes(src));
            string dest = targetEnc.GetString(bytes);
            Row.NonUnicodeCol = dest;
catch (EncoderFallbackException e)
// Log the problematic row ID for later analysis
            ComponentMetaData.FireError(0, "UnicodeConversion",
                $"Row Row.RowNumber: cannot encode character(s) – e.Message", "", 0, out bool cancel);
            // Decide: drop row, set to empty, or copy as is with placeholder
            Row.NonUnicodeCol = string.Empty;

---

If the package runs from an Agent job, create a proxy that runs under the Windows account that already has the proper SSISDB rights. SSIS-661

-- 1. Create a credential that stores the Windows account
EXEC msdb.dbo.sp_create_credential
    @credential_name = N'ETLUserCred',
    @identity = N'DOMAIN\ETLUser',
    @secret = N'YourStrongPassword';   -- only needed for SQL Auth; for Windows, password can be omitted
-- 2. Create a proxy that uses the credential
EXEC msdb.dbo.sp_add_proxy
    @proxy_name = N'ETLUserProxy',
    @credential_name = N'ETLUserCred',
    @enabled = 1;
-- 3. Grant the proxy access to SSIS package subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
    @proxy_name = N'ETLUserProxy',
    @subsystem_id = 12;  -- 12 = SSIS

Then edit the job step → Run as proxy → select ETLUserProxy. If you must stay with a non‑Unicode destination,

If you have a dedicated service account (e.g., DOMAIN\SSISService) that is a member of ssis_admin, you can simply run SSDT → Deploy while logged in as that account. The deployment will succeed because the account already has the needed rights. If the package runs from an Agent job

---