Troubleshooting SSIS-661: A Step-by-Step Guide
If you're encountering issues with SSIS-661, such as package execution failures or errors during data transfer, you're not alone. SQL Server Integration Services (SSIS) is a powerful tool for building enterprise-level data integration and workflow solutions. However, like any complex software, it can sometimes be challenging to troubleshoot.
First, let's clarify what SSIS-661 could refer to. In the context of SSIS, a "package" like SSIS-661 would typically be a collection of tasks and transformations designed to accomplish a specific data integration task. Issues with such packages can arise from a variety of sources, including: SSIS-661
If the package runs from an Agent job, create a proxy that runs under the Windows account that already has the proper SSISDB rights.
-- 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. 4️⃣️ 4
[ ] Verify SSIS version – apply CU with KB‑xxxxx if < 2024‑03.
[ ] Change destination columns to NVARCHAR where feasible.
[ ] If VARCHAR must remain, add a Data Conversion component with explicit code page (1252).
[ ] Add a Script Component to log any Unicode→ANSI loss.
[ ] Enable package logging (OnError, OnWarning) and monitor catalog.operation_messages.
[ ] Run a validation job on a sample payload containing characters like é, ß, 汉.
[ ] Document any rows that get truncated and decide on business rules (drop/replace).
| Attribute | Value |
|-----------|-------|
| Bug ID | SSIS‑661 (internal Microsoft tracking number) |
| Affected components | OLE DB Source, Flat File Source, ADO.NET Source, Data Conversion, Derived Column |
| Symptom | Package fails with error “The conversion from data type Unicode string to non‑Unicode string resulted in a loss of data.” or the task hangs when the pipeline processes rows that contain characters outside the ASCII range (e.g., “é”, “ß”, “汉”). |
| First observed | SQL Server 2016 SP2, but reproduced on 2017, 2019, and 2022 RTM builds |
| Severity | High – data loss can go unnoticed in large‑scale ETL jobs |
Bottom line: SSIS‑661 is a data‑type conversion bug that mishandles Unicode → non‑Unicode casts when the underlying provider (ODBC/OLE DB) returns UTF‑16 strings but the SSIS metadata expects ANSI (DT_STR). The engine incorrectly assumes that the length of the target column is sufficient, leading to buffer overruns or silent truncation. in the Data Flow
| ✅ 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.
| Work‑Around | Steps | Pros | Cons |
|------------|-------|------|------|
| Force Unicode End‑to‑End | - Change destination column to NVARCHAR (or NVARCHAR(MAX) for staging).
- Or, in the Data Flow, add a Data Conversion component and convert the source to DT_WSTR (same length as source) before the destination. | Guarantees no data loss.
Simple to implement. | Requires schema change on destination (may not be feasible in production). |
| Explicit Code Page Conversion | - In the Flat File Connection Manager, set Code Page to 65001 (UTF‑8) and ensure the destination column is VARCHAR.
- Add a Derived Column with TRIM( (DT_STR, 50, 1252) [UnicodeColumn] ). | Keeps destination as non‑Unicode; works for most Latin‑1 characters. | Still fails for characters outside the chosen code page (e.g., Asian scripts). |
| Pre‑load Staging Table | - Load the source into a temporary staging table with all columns as NVARCHAR.
- Use a set‑based T‑SQL INSERT … SELECT to move data to the final table, letting SQL Server handle the conversion (it raises an error if data is lost). | Leverages SQL Server’s robust conversion logic. | Adds an extra step & temporary storage. |
| Script Component (C#) Conversion | - Replace the Data Flow’s built‑in conversion with a Script Component.
- Use Encoding.UTF8.GetBytes() and Encoding.Default.GetString() to control how characters are dropped or replaced (e.g., replace with “?”). | Full control over conversion policy. | Requires custom code; harder to maintain. |
| Upgrade to the Latest SSIS CU | - Install the Cumulative Update (CU) that contains KB‑xxxxxx (see next section). | Fixes the bug at the engine level. | May require a full build/re‑deployment of the SSIS catalog. |
Best practice: If you have the ability to modify the destination schema, the Unicode‑to‑Unicode approach is the safest long‑term solution.