"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:OkWg$QwvFHA.2516@TK2MSFTNGP12.phx.gbl:

> OK So what I see you needing to do is this
>
>
> As the row comes in you need to take a monetary value from your source
> and look up a key in a key table. The keys are allocated to ranges of
> values not discrete values.
>
> Let's go through how to do this
>
> Build the tables
>
> CREATE TABLE KeyProvider
> (
> KeyValue int not null primary key,
> floorvalue decimal(10,2),
> ceilingvalue decimal(10,2),
> Description varchar(255),
> Sort int
> )
>
> GO
>
>
> CREATE TABLE DataTable(col1 int , ColValueToFindInKeyTable
> decimal(10,2))
>
> GO
>
> Add some "key" data
>
> INSERT KeyProvider(KeyValue,floorvalue,ceilingvalue,description,sort)
> VALUES(1,0,500,'0-500',1)
>
> Insert a "source" row
>
> INSERT DataTable VALUES(1,15.2)
>
>
> I will presume the only part that has you foxed is the Lookup.
>
> On the front page add a ref to the KeyProvider table
> Now in the columns tab join the ColValueToFindInKeyTable field to the
> floorvalue field. This just gives us a SQL Statement which we need
> next, it is not important what this is as we will change it now.
> In the Advanced tab enable "Enable memory restriction" and only check
> "Modify SQL Statement"
>
> Change the statement to
>
>
> select * from
> (select * from [dbo].[KeyProvider]) as refTable
> where [refTable].[floorvalue] < ? and ceilingvalue >?
>
>
> Now hit the parameters button and map the ColValueToFindInKeyTable field
> to the two parameters.
>
> Should work (did for me)
>
> Allan
>
>
>
>
>
> "KoryS" <kskistad@hotmail.com> wrote in message
> news:1127334753.925815.58620@g14g2000cwa.googlegroups.com:
>
>
> > I've scoured the 'net and can't find a solution to my problem.
> >
> > Using SSIS, I am trying to load a fact table with surrogate key
> values.
> > But the key is based on $ amounts, so my dimension table contains a
> > "Floor" and "Ceiling" column for defined ranges.
> >
> > Example of dimension table (11 rows):
> >
> > Key Floor Ceiling Description Sort
> > --- ----- --------- ------------- ----
> > 1 0 500 $0 - $500 1
> > 2 500.01 1000 $500 - $1,000 2
> > 3 1000.01 2000 $1,000 - $2,000 3
> > ...
> > 10 10000.01 99999999 > $10,000 10
> > -1 Null Null Unknown 999
> >
> > Because the fact table stores values to the penny, there are
> > theoretically millions of possible values, so creating a dimension key
> > for every discrete value is impractical.
> >
> > The Lookup Transformation only works with exact matches, so that
> option
> > is out.
> >
> > The frustrating thing is, our company uses both Informatica and Data
> > Integrator (from Business Objects), and they have lookup functions
> that
> > handle this without custom programming. Because I use Analysis
> > Services, SQL Server, and Reporting Services, I wanted to standardize
> > on a BI toolset so would prefer to use SSIS when it becomes available,
> > but little issues like this make the case harder to justify.
> >
> > Any suggestions would be greatly appreciated!
> >
> > Thanks
> >
> > Kory