Ssis-661 May 2026

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

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.

| Layer | What’s Going Wrong | |-------|-------------------| | Source Provider | Returns a Unicode (DT_WSTR) buffer regardless of the column definition because the OLE DB driver for Oracle/MySQL always uses SQL_WVARCHAR. | | Metadata Propagation | SSIS metadata engine infers the target data type from the destination schema (VARCHAR → DT_STR). | | Runtime Conversion | The engine performs an in‑memory conversion using WideCharToMultiByte. When the source string contains a character that cannot be represented in the target code page, SSIS‑661 fails to raise a proper exception and either truncates incorrectly or corrupts the internal row buffer. | | Buffer Management | The conversion routine miscalculates the required buffer length for multi‑byte characters, causing buffer overruns that manifest as the “loss of data” error or, on some builds, a hard crash (0xC0047086). | Add a Validation Step

Key takeaway: The bug is not in your mapping logic; it lives in the runtime conversion engine that mishandles Unicode → ANSI when the source length exceeds the target’s byte capacity.

| # | Root‑cause description | How it triggers SSIS‑661 | |---|------------------------|--------------------------| | 1 | Schema drift in the source – columns added, removed, data‑type changed, or column order changed in the source object (table, view, query, flat‑file, etc.) after the package was designed. | When the data‑flow component reads the external metadata at run‑time, it discovers a mismatch with the metadata that was cached at design‑time. | | 2 | Package was deployed to a different environment (DEV → TEST → PROD) where the source/target objects have a slightly different definition. | The component still uses the design‑time metadata (e.g., nvarchar(50)) while the actual column is now nvarchar(100). | | 3 | Changes in a referenced SSIS project/parameter – a package variable, project parameter, or connection manager property that defines a query or file path was altered without re‑validating the data‑flow. | The component re‑generates external metadata based on the new query/path, which no longer matches the cached metadata. | | 4 | Using a dynamic query (e.g., SELECT * FROM dbo.Table WHERE …) together with property expressions that change the query at run‑time. | The component cannot predict the resulting schema, so it falls back to the design‑time schema; the runtime schema is different → error. | | 5 | Metadata cache corruption – rare, but can happen after a package is edited in multiple versions of SSDT/VS or after a forced package load without a full validation. | The component reads an inconsistent cached definition and throws SSIS‑661. | | 6 | Incorrect data‑type mapping in a Data Conversion or Derived Column that forces the component to expect a different physical type than the source actually provides. | The component validates metadata and finds a type mismatch. | Enable SSIS Logging & Event Handlers