SSIS Package was failing with error: The attempt to add a row to the data flow task buffer with error code.

Question

Hi All,
Recently I migrated SQL Server from one server to another and also upgraded from SQL 2016 to SQL 2017.

After migration and upgrade, a few SSIS packages started throwing errors like The attempt to add a row to the data flow task buffer with error code. Can't insert duplicate key in object Table XXX. Refer to the below screenshot.

When I check the source query, I can't see any duplicates. I was clueless about what is happening and then just I change my connection string from Data Source=SERVERName;Initial Catalog=DB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False; to Data Source=SERVERName;Initial Catalog=DB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;

and issue was resolved. Basically It was native client provider causing the issue. Once I changed it to OLEDB provider for SQL. It worked without any issue.

Answer 1

Again It failed and I think It has to do with Memory:

https://support.microsoft.com/en-us/topic/fix-you-receive-error-messages-or-data-is-damaged-after-you-run-an-ssis-package-that-contains-a-data-flow-task-in-a-low-memory-situation-in-sql-server-2005-or-in-sql-server-2008-48df7e5a-f491-9090-9fbf-8213045029a6

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3fde375f-520d-4681-b36c-ed912c064de9/getting-duplicates-from-source-table-but-actually-they-are-no-duplicates-on-the-source-table?forum=sqlintegrationservices

I have tried this: https://sqlsimplify.com/tuning-memory-buffer-settings-in-ssis/

Let see how it goes

But Wait! There’s More!
For those of us using SQL Server 2016 or higher, there is another property that makes this process a lot easier. Scroll back up and take another look at the screenshot of the SSIS data flow properties. There is a property there called AutoAdjustBufferSize. By default, it’s set to false, however setting it to true will instruct SSIS to ignore the value that has been set for DefaultBufferSize and it will adjust the size for you automatically. It bases its value on the DefaultBufferMaxRows setting, so be sure to set a value there. It’s a great time saver for anyone that has the option available to them. Even though it removes a lot of guess work, you still want to test it first.

Answer 2

Is the Integration Services is running on the same physical computer as SQL Server after migration?

Answer 3

Hi @Shivendoo Kumar ,

Can't insert duplicate key in object Table XXX.

Please delete the duplicate data in Table XXX.

Or please insert data into a new destination table.

Please refer to the following link:
I am using ssis and I am getting this error "Violation of PRIMARY KEY constraint .Cannot insert duplicate key in object . The duplicate key value is

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.