| ✅ 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.
# 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 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. SSIS-661
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.
| Role | What It Allows | When to Use |
|------|----------------|------------|
| ssis_admin | Full control over SSISDB (create, delete, deploy, start, stop, view logs) | Development or “owner” accounts. |
| ssis_operator | Execute packages, view logs, but cannot create/alter projects/folders | Production run‑time accounts. |
| ssis_logreader | Read execution logs only | Auditing/monitoring accounts. | | ✅ Check | Why It Matters |
Example: Grant ssis_operator
USE SSISDB;
CREATE USER [DOMAIN\ETLUser] FOR LOGIN [DOMAIN\ETLUser];
EXEC sp_addrolemember N'ssis_operator', N'DOMAIN\ETLUser';
Important: After adding the user, you must refresh the catalog in SSMS (right‑click SSISDB → Refresh) to see the new role.
| Q | A |
|---|---|
| Can I ignore SSIS‑661 by setting ValidateExternalMetadata = False? | Yes, you can, but you lose the safety net that warns you about schema changes. Use this only when the downstream component can truly handle any shape of data (e.g., a script that dynamically reads columns). |
| Does SSIS‑661 appear in the Integration Services Catalog (SSISDB) view? | In SSISDB you will see the error in the catalog.operation_messages view with message_type = 120 and the same error text. |
| Is there a PowerShell or T‑SQL script to locate all packages that might hit SSIS‑661? | You can query catalog.packages for the XML of each package and search for ValidateExternalMetadata="True" combined with components that use * in their SQL. Example: SELECT name, package_id FROM catalog.packages WHERE CAST(package_content AS XML).value('(/DTS:Executable/DTS:Component[@Name="OLE DB Source"]/@ValidateExternalMetadata)[1]', 'int') = 1. |
| What if the source is a flat file that changes column order? | Flat‑File sources also rely on external metadata. Turn on “Retain null values from the source as nulls” and re‑import the column definitions, or better yet, use a Script Component that reads the file dynamically. |
| Will upgrading to the latest SSDT/Visual Studio fix the error? | Upgrading alone will not fix a genuine schema drift; however, newer versions improve the metadata refresh UI and sometimes expose hidden mismatches earlier during design time. | Tip: Keep a test Windows account that mirrors
Add a Validation Step
Enable SSIS Logging & Event Handlers
Automated Unit Tests
Monitor the SSIS Catalog
DT_WSTR → DT_STR. The error moves to the conversion component but the underlying bug remains.