Ssis-661

Error Code: SSIS-661 Error Description: The variable "variable name" was not found in the Variables collection.

// 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;
  • Error 0xC020901C: “The conversion from data type Unicode string to non‑Unicode string resulted in a loss of data.”
  • Or the package finishes with no rows inserted despite a successful execution message (silent data loss).

Step 4 – Resolve the mismatch

| Fix type | When to use | What to do | |----------|------------|------------| | Refresh the component | Schema changed in source, same environment | Right‑click the component → Refresh (or click Validate). This forces SSIS to re‑pull the external metadata. | | Re‑configure the component | Column added/removed, data‑type change | Open the component → Columns tab → remove the old column, add the new one, or adjust the data‑type mapping. | | Update downstream components | Any change cascades to downstream components (e.g., OLE DB Destination, Derived Column) | Repeat the Refresh on each downstream component; if column names change, you may need to re‑map them. | | Use explicit column list instead of SELECT * | Dynamic queries cause hidden drift | Change the source query to list the columns explicitly, e.g., SELECT ColumnA, ColumnB FROM dbo.SampleTable. | | Add a Data Conversion component | Source type changed to a larger type that downstream components cannot handle (e.g., bigint → int) | Convert the column to the expected type before it reaches the failing component. | | Parameterize the query properly | Query built with expressions that may change the schema | Ensure the expression always returns the same column list, or move the query logic to a stored procedure with a stable result set. | | Re‑deploy the package after a full validation | Corrupted metadata cache | In SSDT, right‑click the project → Build → Deploy. Make sure the target server has the latest package version. | | Create a new connection manager (if connection string changed) | Different server/DB version (e.g., SQL 2008 → SQL 2019) | Delete the old connection manager, add a new one, and re‑wire the components. | SSIS-661

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

Solution 2: Check for Typographical Errors

  • Review your package for any typographical errors in variable names.
  • Ensure that variable names match exactly, including case sensitivity.