Ssis-661

By methodically going through these solutions, you should be able to identify and resolve the SSIS-661 error.

Conclusion

The SSIS-661 error can be a challenging issue to resolve, but by understanding its causes and applying the solutions outlined in this article, you can overcome it. Always verify variables, check for typographical errors, validate packages, ensure version compatibility, and repair packages as needed. If you're still experiencing issues, consider seeking guidance from Microsoft support or the SSIS community.

Troubleshooting SSIS-661: A Step-by-Step Guide SSIS-661

If you're encountering issues with SSIS-661, such as package execution failures or errors during data transfer, you're not alone. SQL Server Integration Services (SSIS) is a powerful tool for building enterprise-level data integration and workflow solutions. However, like any complex software, it can sometimes be challenging to troubleshoot.

Run the following queries on the master and SSISDB databases:

-- 1. Is the login mapped?
SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE name = 'DOMAIN\ETLUser';
-- 2. Does a database user exist in SSISDB?
USE SSISDB;
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE name = 'DOMAIN\ETLUser';
-- 3. What roles is the user a member of?
SELECT dp.name AS RoleName, dp2.name AS MemberName
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS dp  ON drm.role_principal_id = dp.principal_id
JOIN sys.database_principals AS dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.name = 'DOMAIN\ETLUser';

Typical missing role: ssis_admin (full admin rights) or ssis_logreader / ssis_operator (limited rights). By methodically going through these solutions, you should

SSIS‑661 is a known bug that causes the Data Flow Task to crash (or silently drop rows) when a source column containing Unicode characters is mapped to a destination column that is defined as non‑Unicode (e.g., DT_STR). The issue typically surfaces in SQL Server Integration Services 2016–2022 when the source is Oracle, MySQL, or a flat‑file encoded in UTF‑8/UTF‑16.

Below is a concise guide that covers:

| 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. | Typical missing role : ssis_admin (full admin rights)

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.