Help using Lookup Transformation

Hi All,

I am doing something really simple and it doesnt work, may be I am missing something, What I am trying to accomplish is to load a fact table using lookup transaformation, however my source data was different from the data in my dimension (or the datatype ) I had to use a data conversion task before my lookup , so the data flow is something like this source -> Data Conversion -> Lookup -> destination , I am getting an error at my lookup task where it says the "[Lookup [82]] Error: Row yielded no match during lookup". and then it just fails. I know for sure that there has to be matching data. donno what is it that I am missing.

Thanks

Things to look out for...

Trailing spaces on either side of the lookup (source data or lookup data). If one has trailing spaces and the other doesn't, it won't match.

CaSE. The lookups are case sensitive.|||

Hi,

I am pretty sure that the case is matching and also I have tried using LTRIM(RTRIM) for the trailing spaces, still getting the same error, any thing else I need to look into?

Thanks

|||

db_guy wrote:

Hi,

I am pretty sure that the case is matching and also I have tried using LTRIM(RTRIM) for the trailing spaces, still getting the same error, any thing else I need to look into?

Thanks

Redirect the errors (error output, red arrow) to a flat file source, OLE DB destination, or even a Row Count Transformation, and add a data viewer to inspect the data that does not match.|||

I am still struggling with the loading this fact , the problem I have now is the package runs succesfully but nothing gets loaded into the fact, I am redirecting the rows into a rows count transformation but how do I see whats not getting into the fact or how do I see the values in those variables.

Thanks

|||

db_guy wrote:

I am still struggling with the loading this fact , the problem I have now is the package runs succesfully but nothing gets loaded into the fact, I am redirecting the rows into a rows count transformation but how do I see whats not getting into the fact or how do I see the values in those variables.

Thanks

Add a data viewer to the red arrow connecting the lookup to the row count. (Double click on it and select data viewers, then add a new data viewer)|||

Hi,

I have attached data viewers and one thing I noticed is they behave in a weird way, sometimes they have 0 records and sometimes they have data, donno why.I have three lookups that I use , and then finally they all load the fact table

Thanks

|||Connect all lookup error flows to a Union All component, and then to a row counter. Add the data viewer between the union all and the row counter. This will let you see all of the lookup error outputs.

Or, just hook each lookup up to their own row counter and add data viewers there. You're data is getting caught up somewhere.|||

I think using the data viewers I have narrowed down the problem thanks for that, however I have another question how do I do lookup where I have a join which is something like this

INNER JOIN dbo.Dim_Time T WITH (NOLOCK)

ON SUBSTRING(T.MonthYear,1,3) = SUBSTRING(s.Fiscal_Time,1,3)

AND SUBSTRING(T.MonthYear,6,2) = SUBSTRING(s.Fiscal_Time,6,2)

The problem is I was using derived column to create/replace the stage column using the substring function , however I cannot do that twice because derived column cannot be used twice for the same column, any suggestions?

Thanks

|||

Where did you get that you can't use a derived column twice for the same column?

If you are having a problem with your lookup you can always use a Source / merge join transform, or you can go to the advanced tab of the Lookup transform and select enable memory restriction and type in your exact query with parameters and line it up that way (This is much slower than using it in the regular lookup mode)

If you were to give more info we could probably tell you the exact steps needed to complete this process.

(also, if your original question was answered, remember to mark it as such, along with all other applicable answers)