SSIS transaction from retional table to the fact_buy

Goal:

Fill data in the table Fact_buy.

Problem:

How should I make a data transaction based on the sql code below to the table Fact_buy in SSIS?

The sourcecode below is indirectly that you cannot make a transaction direcly in the table Fact_buy for instance you have the data of store_id that is located in the table Dim_stor开发者_开发技巧e.

You need to retrieve data of id_dimstore and id_diproduct in order to make transaction to the table Fact_buy

What I believe is that you need to make more complementory by using additional component, sql etc in order to complete the transaction to the Fact_buy.

SELECT store_id, product_id, buy Id, date
FROM   Store, Buy, Product

The component you are looking for is a Look Up found inside a Data Flow Task. This will allow you to look into the Dim_store, match your data to a record in the dim, and return the id_dimstore.

Your data flow task could possibly look like this:

1. ADO .Net/OLE DB Source
2. Look Up for Store (gets store_id)
3. Look Up for Products (gets product_id)
4. Derived Column (add date to each row -- could also be done in source)
5. ADO .Net/OLE DB Destination

There are alternatives to what I have suggested, however, this is a really simple solution.