I have a package that is loading a data warehouse table. The source is from a SQL 2008 database, it has 4 columns where I need to replace the business keys with surrogate keys. The destination is also SQL 2008. I have 2 data flow tasks. The first data flow task handles the caching of the 4 look up tables. The 2nd data flow task contains an OLE DB source to read the data from the source, 4 look up transformation (all full cache mode and using their respective cache managers). There are 2 columns (the business key and the surrogate key) in the available lookup column (from the lookup table). The business key in the look up table is mapped to the business key in the available input column. The lookup operation adds the surrogate key as a new column. Everything works beautifully till insertion point at the destination. Using the data viewer, it is the business key (and not the surrogate key) that is being loaded to the destination columns. This makes the package fail because of the validation on the destination column Source Column ProductID varchar Lookup Column ProductID varchar - BusinessKey ProductKey int - surrogate key DestinationColumn ProductKey int - surrogate key Instead of ProductKey it is ProductID that is being returned at the insertion point I have checked the column mapping on the OLE DB destination, it is perfect. The business key data type is nvarchar and the surrogate key is int. But when I run the package, I don't know what happens to the surrogate keys. I have replicated this on a different laptop (SQL server 2008, 64 bit) the package ran without any issues Could some please explain what is happening? Am I missing some setting? I am really baffled Thanks in anticipation of your help

Could you post the error message here? Let me summarize the issue: Source: ProductID A B LookUp: ProductID, ProductKey A 1 B 2 Destination: ProductKey 1 2 However, the Lookup return ProductID as ProductKey, so you cannot insert ID to the Destination, right? Anyway, could you use data viewer to check if LookUp transformation returns correct results? Thanks, Raymond Raymond Li - MSFT

Thanks Raymond, I have done that. The data viewer shows everything is alright. The error message posted is "The INSERT statement conflicted with the FOREIGN KEY constraint " But I have looked at other information posted in the execution result tab, one is "[SSIS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 16 buffers were considered and 16 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked." Could that explain it? Many thanks

error message posted is "The INSERT statement conflicted with the FOREIGN KEY constraint " This is a typical FOREIGN KEY conflicting error, right? It means the member you inserted in fact table doesn’t exist in related dimension table. Please check FOREIGN KEY, ensure the member in inserted exists in dimension indeed. Thanks, Raymond Raymond Li - MSFT

Merci beaucoup!