Ssis-661

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

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.

| 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 | SSIS-661

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.

If you have a dedicated service account (e.g., DOMAIN\SSISService) that is a member of ssis_admin, you can simply run SSDT → Deploy while logged in as that account. The deployment will succeed because the account already has the needed rights.

| ✅ 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. | | Role | What It Allows | When

Tip: Keep a test Windows account that mirrors the production service account. Use it to validate permissions before rolling out changes to production.

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

| Technique | How to implement | |-----------|-----------------| | Schema validation at the start of the package | Add an Execute SQL Task that runs SELECT TOP 0 * FROM dbo.Table and checks sys.columns via a script task; raise an error if a mismatch is detected. | | Version‑controlled source objects | Keep a DDL script in source control and enforce a build‑time check that the production object matches the script. | | Explicit column list in all sources | Never use SELECT *. | | Package‑level data‑type constraints | Use the Data Flow → Advanced → Data Type property to lock a column to a specific type. | | Deploy with “ValidateExternalMetadata = False” (cautiously) | In scenarios where you know the schema will change but you want the package to continue, set the property on the component, but be aware you lose early detection. | | Continuous Integration (CI) testing | Add a step in your CI pipeline that runs the package against a test copy of the production database and fails the build on any SSIS‑661 (or other) error. |