Loading Fact Using Lookup

Hi All,

I have to load my fact table using data from my stage table and joining it against dimesnsions, most of my dimesions are straight joins that I can implement using Lookups , howevere in one join I am using something like this


SELECT T.Dim_Time_ID ,

SUM (Measure)

FROM [dbo].[Stage_Table] S

INNER JOIN Dim_Time T


ON SUBSTRING(T.MonthYear,1,3) = SUBSTRING(S.Time,1,3)

AND SUBSTRING(T.MonthYear,6,2) = SUBSTRING(S.Time,6,2)

Currently I am using a copy column transformation to make a copy of this column, and then using substring function in my SQL code and the Lookup Transformation joining the data to get the desired output, was wondering if there is any other (better) way of accomplishing this inside the data flow.

Thanks