Hi, I have not figured out this one yet. Have done some simpler packages. Need your expertise one this. To simplify my requirements, I have two tables as following. I need to look up by the dollar amount in table A and the range in table B, so that I can later pivot table A by the dollar amount range. I have done simple lookups on one field. BTW, table B is the way I think it should be designed. It can be different depends on your suggestions. Table B reads from a text file. The dollar break up can change over time. Thank you for your time Table B tbl_Fact qtr_id week id Cust_ID Cust Amount Q1 Week1 1234 ABC 34626.08 Q1 Week2 1234 ABC 0 Q1 Week3 1234 ABC 11456.7 Q1 Week4 1234 ABC 84731.96 Q1 Week5 1234 ABC 147982.49 Q1 Week6 1234 ABC 160608.07 Q1 Week7 1234 ABC 30296.65 Q1 Week8 1234 ABC 210156.56 Q1 Week9 1234 ABC 128525.35 Q1 Week10 1234 ABC 0 Q1 Week11 1234 ABC 772153.14 Q1 Week12 1234 ABC 576933.11 Q2 Week1 990 XXX 0 Q2 week4 990 XXX 72 Q2 week5 990 XXX 58 Q2 week7 990 XXX 473.12 Q2 week9 990 XXX 472.68 Q2 week10 990 XXX 319 Q2 week11 111 XXXXX 929.12 Q2 week12 111 XXXXX 400.68 Table B: Dim_Purchase_Level ID Min Max Description 1 -1000000 499 <500 2 500 999 500-999 3 1000 2499 1000-2499 4 2500 4999 2500-5000 5 5000 100000 >5000 Sincerely Thank You !

A look up with value range seems to me a viable solution: http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-rangeArthur My Blog

There are also a few other alternatives shown on the SSIS Team Blog. Talk to me now on

Hi, thanks for the link. for the most part of it makes sense. Though I am not sure how to relate the tables by the natural key. In this case, Table B -> ID is not in Table A. During the fact table generation, what needs to be done? Seems to me, I need to do the lookup to locate the naturalID during the fact table loading. If I can do that, then I can follow the rest of the link's solution. Please let me know if I am missing something here. thanks for your help! Sincerely Thank You !

The Lookup logic works by looking up two sources of data having "something" in common. This "something" could be a single column (usually a Key) or a combination of columns(i.e a composite key). In order to compare apples with apples, we need to have apples at the first place. So in your case, you'll need to identify a way of having some commonality between the tables you are going to use. In Datawarehousing design, more often that not the tables involved will be linked in a Fact-table:Dimension-table(s) relationship. Hence, each fact table has one column each from all the dimension tables. These columns are usually Key columns of the Dimension tables involved. Hence, a FactFInance table will have DimEmployeeKey, DimDepartmentKey and so on. And these columns will be the Key columns of the individual DimEmployee and DimDepartment dimension tables. Hope this helps. Cheers!! Muqadder.

Hi, In this case, the relationship between the two tables is by the Dollar Amount. Sorry, the two tables are still in the staging table stage. In table B, though I prefixed it dim, it is really not yet a dimension table. Table A is the raw data table. sorry for the confusion. The ID in Table B is the natural key. I don't want to populate Table B with a natural key from -1000000 to 1000000000000000000000 in order to link it to the Dollar amount field in table A (I'd need to round up the amount). That would not be viable. How do I somehow grab the Dollar Amount, and fit them into the defined ranges, then say add a derived column in table A with the range description? If I can do that, then it would be a good start. thanks again! Sincerely Thank You !