Lookup Transform

Hi!
I am a newbie, grateful for some help. I have a Source Ole DB w sql-command selecting the customer.salary and customer.occupation, which I want to match with demo_id in Ole DB destination.
salary, occupation also in dim_demographic.
But in Lookup editor I find no column demo_id... how do I do this?The lookup must select against your table with demo_id in it... We need more information here... If you can't find demo_id in your lookup table, and you need it to be there, well then you probably have the wrong table defined in your lookup.|||

Not sure what your problem is. In general, Lookup transform can be basedon a table or on a query; if you don't see an specifc column in the Column is because that column is not on that table/query.

Could you provide more details on how you have set up your package.

BTW, Have you considered to just include the lookup table as a part of the OLE DB Source query and not use the lookup transform at all?

|||Ihave a select customer.salary, customer.occupation in source DB. These should be Lookup columns matched with the id from dim_demography. How?|||The source has nothing to do with the lookup.

In the lookup, you need to specify a lookup table or query. When that's done, then you map columns from the dataflow (your source) to columns in the lookup table. You'll have to have an ID column coming from the source query though.

I'm sorry, but your response didn't say anything more clear than your original post.|||How would you do if you had a demo_id in db Destination, which should be matched with the values salary and occupation in Dim_demographic (containing columns id, salary, occupation) or de db Source sql customer.salary, customer.occupation?|||Please provide your schema:

Table1:
Column1
Column2
....

Table2:
Column1
Column2
...

We'll go from there.|||OLE db Source column:
SELECT Sum, k.sum-p.price AS Profit, convert (char(8),date,112) as Date_id, salary, occupation, k.shop_id, k.customer_no, salesperson_id, p.articlenr,campain_id
FROM Purchase k join product p on k.articlenr=p.articlenr join customer ku on k.customernr=ku.customernr

Destination column:
demo_id, Profit, customer_id, product_id, shop_id, date_id, sum, salesperson_id, campain_id

Demo_id = id from Dim_demographic.
Dim_demographic columns are id, salary, occupation

How do I get the Dim_demographic_id to match demo_id? by comparing salary and occupation from Source column with Dim_demographic salary, occupation.. may be in an sql in Lookup?
What should be used in Lookup as connection table? or SQL Select demographic.id where demographic.salary= fact_purchase.salary...
|||

curiousss wrote:

OLE db Source column:
SELECT Sum, k.sum-p.price AS Profit, convert (char(8),date,112) as Date_id, salary, occupation, k.shop_id, k.customer_no, salesperson_id, p.articlenr,campain_id
FROM Purchase k join product p on k.articlenr=p.articlenr join customer ku on k.customernr=ku.customernr
Destination column:
demo_id, Profit, customer_id, product_id, shop_id, date_id, sum, salesperson_id, campain_id
Demo_id = id from Dim_demographic.
Dim_demographic columns are id, salary, occupation

How do I get the Dim_demographic_id to match demo_id? by comparing salary and occupation from Source column with Dim_demographic salary, occupation.. may be in an sql in Lookup?
What should be used in Lookup as connection table? or SQL Select demographic.id where demographic.salary= fact_purchase.salary...

Use a lookup transform with a query like:

Take the output of the OLE DB Source component to a Lookup transform. Inside of the lookup write a query like:

Select dim_demographic_id, salary, occupation
From Dim_demographic

Then in the columns tab draw a line to join Salary and occupation; in the bottom part, choose id from the dropdown list to bring the dim_demographic_id (from dim_demographic) to the pipe line. Now when you connect the pipeline to the destination component you should have that extra column; all you have to do is to create the mapping between thedestination column demo_id and the column in the pipeline.

BTW, it is better to provide a query inside the lookup tranform than select the tablename from the list...it save resources(memory) improvng performance.

|||Thank you!
Now lines bw Available lookup (salary, occupation) and matching Available Input columns. Available Lookup id marked, and on first line in Lookup column: id, Lookup operation: <add new column>

But OLE db destination Preview is empty. although Destination column: demo_id is matched with Input column: id

Execution error:
[Lookup [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

[OLE DB Destination [3924]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [3924]] Error: There was an error with input column "salesperson_id" (3972) on input "OLE DB Destination Input" (3937). The column status returned was: "Conversion failed because the data value overflowed the specified type.".|||The OLE DB destination preview shows you what's in the destination table, not what's in the data flow. You have nothing in your table, hence nothing shows up in the preview.|||

curiousss wrote:

Thank you!
Now lines bw Available lookup (salary, occupation) and matching Available Input columns. Available Lookup id marked, and on first line in Lookup column: id, Lookup operation: <add new column>

But OLE db destination Preview is empty. although Destination column: demo_id is matched with Input column: id

Execution error:
[Lookup [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

[OLE DB Destination [3924]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Destination [3924]] Error: There was an error with input column "salesperson_id" (3972) on input "OLE DB Destination Input" (3937). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Ok Few things here:

The preview in the OLE DB Components shows data that is already in that table; if this is your initial load, the table is empty, then the preview will show nothing.

The first message you in the execution error is warning you about duplicates in the Dim_Demographics; meaning, there is more than one row for a combination of salary and occupation. This is treated as a warning as SSIS will use any of those values; so be careful on that. Provide a query and joins that retrieves unique values.

The other 2 messages are actually errors; check the data types of the source and destination for salesperson_is column; they have to be the same.

|||Hi!
Thank you.. its getting late...wouldn't this eliminate duplicates?
SELECT DISTINCT id, salary, occupation
From Dim_demographic|||by the way.. is there any logic/rule/order when changing data types (just shows error now)

Purchase Source both salesperson_id are DT_18

Purchase Destination salesperson_id Input DT_18
Purchase Destination salesperson_id external DT_14

...Dim_salesperson salesperson_id is DT-14|||

I don't think the Distinct will eliminate the duplicates as each row (I guess) has a unique Id value.

To convert data types there is a Data conversion tranform in the toolbox of the data flow.