Home Forums Data Warehousing Integration Services SQL with Always Availability option ON causing truncate and load packages FAIL RE: SQL with Always Availability option ON causing truncate and load packages FAIL

  • Siddarth V (7/25/2016)


    Possibility of duplicate incoming data is zero.

    I've heard that before. Often followed later by finding out that something had changed somewhere upstream and there now were duplicates

    If you're getting primary key violations then there's already data in the table matching incoming, or there's duplicates in the incoming data. Availability Groups won't cause primary key violations. So in your case, either the truncate is failing, or there's duplicates in the incoming data.

    Add some logging to your package to put any data failing into an errors table for analysis and to log any failures of earlier steps. That'll help pin down where the duplicates are.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass