Search code examples
sql-serverssislookupdata-warehousefact

SSIS - Cannot insert duplicate key in object 'dbo.FACT_TABLE'

I know that we can't have duplicate records in Fact Table but I'm very new in SQL Integration Services and I'm looking for a package that can recognize that if the surrogate keys already exists in fact tables...

In this moment I've this package:

enter image description here

Each lookup objects get the business key of each dimension in my datawarehouse.

At the first time it run very well because in Fact Table I don't have records, but next I made the test and I run the same data again (because in future I want to run this package every 10 minutes so it will get the same data ofentimes) and I get the following error (that I understand very well I want to build an approach to handle this automatically):

[Load into dbo_DimCI [144]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_FACT_FACT_TABLE'. Cannot insert duplicate key in object 'dbo.FACT_TABLE'. The duplicate key value is (337, 44, 3, 19, 4682, 12).".

Which objects I need to insert in my package in order to handle this error?

Solution

  • I believe your intention is to load only the deltas in each load.

    Which means if a record already exists in the fact table, then the record should not be inserted again.

    You could achieve this by using a Merge Query.

    I do not have an example to load a fact table, but I can point you to an example of a post that talks about loading a Type 2 Dim Table.

    Check this link:https://www.mssqltips.com/sqlservertip/2883/using-the-sql-server-merge-statement-to-process-type-2-slowly-changing-dimensions/

    You should be able to use such a statement in your SSIS package to update your fact table.

    Happy to answer any further questions.

    Cheers Nithin