Demographics Dimension Table - Finding the Key! I have a dimension table which captures demographics on Employees.  The schema is similar to below:

    CREATE TABLE [dbo].[DimDemographics]
(
    [DemographicsKey] [int] NOT NULL,
    [AgeBand] [nvarchar](50) NULL,
    [LowerAge] [tinyint] NULL,
    [UpperAge] [tinyint] NULL,
    [SalaryBand] [nvarchar](100) NULL,
    [LowerSalary] [int] NULL,
    [UpperSalary] [int] NULL,
    [Gender] [nvarchar](50) NULL,
    [MaritalStatus] [nvarchar](50) NULL,
)


In the AgeBand, you'll have values such as '25 or Less', '26-30', 31-35'....'Over 65'
The LowerAge and UpperAge are just to allow us to query, i.e '25 or Less' will have a LowerAge of 0 and an UpperAge of 25 and so on.

The SalaryBand, LowerSalary and UpperSalary follow the same principal as AgeBand.

In T-SQL, we've written a query which joins the Employee table to the Demographics table and can extract the DemograhicsKey based on their age, salary, marital status and gender.

The Fact table in question has an Employee Key and a Company Key and we've obtained these with lookups - No problem here.

The problem is how to get a DemographicsKey in SSIS.  We've tried lookups, OLE DB Commands.  The SQL Query is similar to

    SELECT d.DemographicsKey
FROM dbo.DimEmployee e
INNER JOIN dbo.DimDemographics d on 
    e.Age &gt;= d.LowerAge and e.Age&lt;=UpperAge
    and e.Gender=d.gender
    and e.Maritalstatus = d.MaritalStatus
    and CAST(e.SalaryReferenceLocal AS FLOAT) &gt;= d.LowerSalary and CAST(e.SalaryReferenceLocal AS FLOAT) &lt;= d.UpperSalary
    and DATEPART(YEAR, DATEADD(YEAR, -e.Age, GETDATE())) &gt;= d.LowerYear AND DATEPART(YEAR, DATEADD(YEAR, -e.Age, GETDATE())) &lt;= d.UpperYear
WHERE e.EmployeeID =?


The ? is because of the OLE DB Command.  I was trying to do it here, but could not get any output from a T-SQL query input.  One consideration was to use a Stored Procedure with OUTPUT, but at this point, I'd prefer to avoid that approach and solve it (if possible) with transformations!

I'm out of ideas now as to how to obtain a key from the demographics based on a list of criteria.

For reference, the table and code is scaled down and not the full version, but has enough information to describe the problem.

Any help would be greatly appreciated!

Thanks!
You mention that you already have a piece of sql that does what you want. 

So i would suggest you modify that to become an update statement and implement it as a sql task. Another option is to save the update within a stored proc and call/execute it from ssis.

With a bit of thought you could probably write an update statement that only sets the values for new records or for records that have changed. Or perhaps do it as part of your ETL insert/merge.

However going back to my other comments: i don't think it makes sense to have a single key or dimension for such diverse attributes. I suggest you have a look at some sample schemas to see if it could be done differently.