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

Shivendoo Kumar 736 Reputation points
2021-02-07T20:33:15.673+00:00

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.

64996-capture43.png

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,462 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Dilip Divakaran 76 Reputation points
    2021-02-07T21:53:29.067+00:00

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


  2. Monalv-MSFT 5,896 Reputation points
    2021-02-08T07:21:46.797+00:00

    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.


  3. Shivendoo Kumar 736 Reputation points
    2021-02-10T05:20:18.987+00:00

    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.