COLOMN FOREIGN KEY conflict

[Tassajara]

Hi there,

I have a problem loading a fact table in a datawarehouse. The dimension tables all have unique values and an ID. Maybe I'm overlooking a very simple problem but after several tries the statment is still terminated. The notification I get:

INSERT statement conflicted with COLOMN FOREIGN KEY constraint ‘FK_FACT_INVOICE_DIM_RELATION_AGENT’. The conflict occurred in database ‘CM_DWH’. Table ‘DIM_RELATION’. Colomn ‘REL_NR’

The SQL statement I'm using to select the data is the following:

SELECT ajob.[ajob-trp-date], atrj.[atrj-trans-date],
ajob.[port-code-org], atrj.[atrj-lclfcl], ajob.[bm-code],
ajob.[naw-code-agt], ajob.[ajob-agt-name], ajob.[naw-code-cm],
ajob.[ajob-cm-name], ajob.[naw-code-cons],
ajob.[ajob-cons-name], ajob.[naw-code-shp],
ajob.[ajob-shp-name], ainl.[bc-code],
SUM(ainl.[ainl-sale-amt-co]) AS SALE_AMOUNT,
SUM(ainl.[ainl-cost-amt-co]) AS COST_AMOUNT,
SUM(ainl.[ainl-accr-amt-co]) AS ACR_AMOUNT
FROM ainl LEFT OUTER JOIN
ain ON ainl.[ain-doc-no] = ain.[ain-doc-no] LEFT OUTER JOIN
ajob ON ain.[ajob-doc-no] = ajob.[ajob-doc-no] AND
ain.[job-no] = ajob.[job-no] AND
ain.[job-sub-no] = ajob.[job-sub-no] INNER JOIN
atrj ON ajob.[ajob-doc-no] = atrj.[ajob-doc-no] AND
ajob.[job-no] = atrj.[job-no] AND
ajob.[job-sub-no] = atrj.[job-sub-no]
GROUP BY ajob.[ajob-trp-date], atrj.[atrj-trans-date],
ajob.[port-code-org], ajob.[port-code-dest], atrj.[atrj-lclfcl],
ajob.[bm-code], ajob.[naw-code-agt], ajob.[naw-code-cm],
ajob.[naw-code-cons], ajob.[naw-code-shp], ainl.[bc-code],
ajob.[ajob-agt-name], ajob.[ajob-cm-name],
ajob.[ajob-cons-name], ajob.[ajob-shp-name],
ainl.[ainl-sale-amt-co], ainl.[ainl-cost-amt-co],
ainl.[ainl-accr-amt-co]

This works fine, I get all the data I need. After this I'm using an ActixeX script with Lookups. A part of the script is this:

Dim varRelNrAgt
varRelNrAgt = (DTSLookups("lkp_rel_nr_agt&quot.Execute(DTSSource("naw-code-agt&quot))
If IsNull (DTSLookups("lkp_rel_nr_agt&quot.Execute(DTSSource("naw-code-agt&quot)) Or IsEmpty (DTSLookups("lkp_rel_nr_agt&quot.Execute(DTSSource("naw-code-agt&quot)) Then
DTSDestination("REL_NR_AGENT&quot = "0"
Else
DTSDestination("REL_NR_AGENT&quot = DTSLookups("lkp_rel_nr_agt&quot.Execute(DTSSource("naw-code-agt&quot)
End If

This is the statement of the lookup:

SELECT REL_NR
FROM DIM_RELATION
WHERE REL_CODE = ? AND REL_TYPE = 'Agent'


I hope someone can give me a possible solution.

Thanks