During ETL detecting dirty records from Fact

Attribute Key Not found Error occurs in SSAS due to the following reasons :

1. Dimensions processed after measure groups (This one is simple, just process Dimension before processing a Cube)
2. Presence of Orphan Records in Fact Table  (This one is tuff to handle from SSAS Perspective)

These errors can be dealt easily at ETL Level, sometimes we got orphan records in Fact table which means we got a Key in Fact for which no entry in Dimension or Master Table.

We can use Lookup Task in SSIS to deal with such scenarios.

1) In Data Flow add OLE DB Source pointing to source table, in our case its SCD.DBO.SALES

2) Add Lookup & Connect Source to Lookup

In General Tab Select Cache Mode and Connection Type

3) Connection Columns Select the Table in our case its DestinationEmployees

4) Then Select Column Mapping (Join)

5) Add two Destinations, one is pointing to Fact Table and another is pointing to Orphan Table

Just for Testing, I have added 2 File Destination.

After completion, Package will look something like this.

Sample.zip