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.
SSIS‑661 is a known bug that causes the Data Flow Task to crash (or silently drop rows) when a source column containing Unicode characters is mapped to a destination column that is defined as non‑Unicode (e.g., DT_STR). The issue typically surfaces in SQL Server Integration Services 2016–2022 when the source is Oracle, MySQL, or a flat‑file encoded in UTF‑8/UTF‑16.
Below is a concise guide that covers:
Run the following queries on the master and SSISDB databases:
-- 1. Is the login mapped?
SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE name = 'DOMAIN\ETLUser';
-- 2. Does a database user exist in SSISDB?
USE SSISDB;
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE name = 'DOMAIN\ETLUser';
-- 3. What roles is the user a member of?
SELECT dp.name AS RoleName, dp2.name AS MemberName
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS dp ON drm.role_principal_id = dp.principal_id
JOIN sys.database_principals AS dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.name = 'DOMAIN\ETLUser';
Typical missing role: ssis_admin (full admin rights) or ssis_logreader / ssis_operator (limited rights). SSIS-661
| ✅ Check | Why It Matters |
|----------|----------------|
| SQL Server version – at least SQL Server 2012 (SSISDB introduced) | Older versions use legacy file‑system deployment, which surfaces a different set of permissions. |
| SSIS Catalog (SSISDB) created (CREATE CATALOG) | The error is usually thrown when the Catalog exists but the caller lacks rights. |
| Windows account – the one you’ll run the package under (e.g., DOMAIN\ETLUser) | Permissions are granted to Windows or SQL logins, not to AD groups unless you map them. |
| SQL Server login – a login mapped to the Windows account (or a contained DB user) | The login must have a user in SSISDB with the needed role membership. |
| SQL Server Agent proxy (if using Agent jobs) – proxy with a credential that stores the Windows account | Without a proxy, the job runs under the SQL Agent service account, which often lacks rights. |
| Data source credentials – stored either in package connection managers, Project‑level Parameters, or SSISDB Environment Variables | The package may still fail later if those credentials are missing, even after fixing the Catalog permissions. |
Tip: Keep a test Windows account that mirrors the production service account. Use it to validate permissions before rolling out changes to production. If you have a dedicated service account (e
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;
