Ssis-661

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

# Requires SqlServer module
Import-Module SqlServer
$server   = "MySqlInstance"
$database = "SSISDB"
$query    = @"
DECLARE @eid BIGINT;
EXEC catalog.create_execution 
    @package_name = N'MyPackage.dtsx',
    @execution_id = @eid OUTPUT,
    @folder_name = N'MyFolder',
    @project_name = N'MyProject',
    @use32bitruntime = 0;
SELECT @eid AS ExecutionID;
"@
try 
    $result = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -ErrorAction Stop
    Write-Host "✅ Execution created with ID $($result.ExecutionID)"
catch 
    Write-Error "❌ Failed to create execution: $_"
``  
Add this script to your nightly CI pipeline; a non‑zero exit code will break the build, alerting you instantly if permissions drift.
---
## 7️⃣  Advanced Scenarios
### 7️⃣️ 7.1. Azure‑SQL Managed Instance + SSIS Integration Runtime (IR)
| Situation | What changes |
|-----------|--------------|
| **Deploying to Azure‑SSIS IR** | Permissions are managed via **Azure AD**. The Azure‑SSIS service principal must be granted **Contributor** on the SSIS IR resource and **db_owner** on the SSISDB database. |
| **Running packages that access Azure Blob Storage** | Use **AzureKeyVault** or **Managed Identity**. Grant the IR’s Managed Identity `Storage Blob Data Reader` (or Writer) on the storage account. |
| **Error 661 from Azure IR** | Usually means the **Azure AD token** cannot be fetched. Verify the IR’s Managed Identity is enabled and has the required role assignments. |
#### Fix Example (Azure CLI)
```bash
# Grant IR's managed identity the Storage Blob Data Reader role
az role assignment create \
  --assignee <IR-managed-identity-object-id> \
  --role "Storage Blob Data Reader" \
  --scope /subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Storage/storageAccounts/<storage-account>

[ ] 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).

SSIS‑661 is a classic example of how subtle Unicode handling bugs can ripple through large data‑integration pipelines, causing silent data corruption. By:

you can eliminate the risk, keep data integrity intact, and avoid costly downstream fixes.

typically refers to a specific entry or record within the Social Service Information System (SSIS)

, a case management tool used by state agencies (notably in Minnesota and Kansas) to manage child welfare, adult protection, and healthcare services. Kansas Department for Children and Families (.gov)

Because "SSIS-661" is often a system-generated ID for a specific state manual or form, the most useful guides are those provided by the relevant state departments: 1. Key System Guides & Documentation

If you are looking for general instructions on how to use the SSIS platform for data entry or case management: SSIS User's Guide (OFM): This is the primary manual for the Social Service Information System , covering fiscal management and case reporting. SSIS Desk Aid: SSIS-661

A condensed version of the manual designed for quick reference during daily tasks. Official State Portals: In Minnesota, for example, the MN Department of Human Services

provides a specialized "Worker Documentation" guide for specific form IDs. Kansas Department for Children and Families (.gov) 2. Common Contexts for SSIS IDs "SSIS-661" may appear in your workflow as: A Fiscal Form:

Often related to payment requests or reimbursement for social services. A Case Review ID:

Used in Quality Control (FRQC) imaging or federal review reporting. A Technical Error Code: If you are a developer using SQL Server Integration Services (SSIS)

, this ID might correspond to a specific project or package error, though it is not a standard Microsoft system error code. Microsoft Learn 3. Actionable Next Steps Verify the State: Confirm if you are working within the

human services system, as their documentation numbering differs. Consult Local Admins: Most agencies have a dedicated SSIS Mentor Fiscal Contact

who maintains the latest version of specific forms like the 661. | Attribute | Value | |-----------|-------| | Bug

Could you clarify if you're looking for help with a specific state form or an error message in a data integration project? SQL Server Integration Services (SSIS) - Microsoft Learn

SQL Server Integration Services - SQL Server Integration Services (SSIS) | Microsoft Learn. Microsoft Learn CFS PPM2 - Kansas Department for Children and Families

Troubleshooting SSIS-661: A Comprehensive Guide

Microsoft SQL Server Integration Services (SSIS) is a powerful tool for building enterprise-level data integration and workflow solutions. However, like any complex software, SSIS can encounter errors that hinder its performance. One such error code is SSIS-661, which can be frustrating to resolve without proper guidance. In this article, we'll explore the possible causes of SSIS-661 and provide step-by-step solutions to help you troubleshoot and overcome this issue.

Understanding SSIS-661

The SSIS-661 error code typically occurs when there's a problem with the package validation process in SSIS. This error can manifest in various scenarios, such as:

The error message associated with SSIS-661 often reads: "The variable cannot be found. Verify that the variable exists in the Variables collection and has not been deleted." [ ] Verify SSIS version – apply CU

Causes of SSIS-661

Based on Microsoft documentation and community feedback, here are some common causes of the SSIS-661 error:

Solutions to Resolve SSIS-661

To resolve the SSIS-661 error, follow these step-by-step solutions:

If you’re launching the package via DTExec from a command line, run:

whoami

to see the Windows identity. Then verify that identity in SSISDB as above.

If you’re using SQL Agent:

SELECT name, credential_id
FROM msdb.dbo.sysjobs
WHERE name = 'YourJobName';

Then inspect the linked proxy and its credential.