Find out the not matching key value in look up

Question

Find out the not matching key value in look up

Charmer

SSChampion

Points: 14903
More actions
August 22, 2017 at 8:04 am

#323188

Friends,
My SSIS package is loading data into DWH on daily basis. On one of the package , inside the data flow, there are 4 key columns are joined against the reference table using look up. Now my requirement is to find out the reason behind the non matching rows. I mean , I want to find out, which key value is different from the other. I believe it is possible using script component instead of using few more look up components to find out the mismatched value.
Any suggestions would be appreciated.
Thanks,
Charmer

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

Charmer - Tuesday, August 22, 2017 8:04 AM
Friends,
My SSIS package is loading data into DWH on daily basis. On one of the package , inside the data flow, there are 4 key columns are joined against the reference table using look up. Now my requirement is to find out the reason behind the non matching rows. I mean , I want to find out, which key value is different from the other. I believe it is possible using script component instead of using few more look up components to find out the mismatched value.
Any suggestions would be appreciated.

Redirect the mismatches to another table rather than failing the lookup, in the event of mismatches. No need for a script component.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Charmer SSChampion Points: 14903 More actions · Answer 2

Hi Phil,

It's already redirected. But now they want to do this on the go. They want to remove look up and use script component and identify the miss match key values.

Thanks,
Charmer

Phil Parkin SSC Guru Points: 246970 More actions · Answer 3

Charmer - Tuesday, August 22, 2017 9:55 AM
Hi Phil,
It's already redirected. But now they want to do this on the go. They want to remove look up and use script component and identify the miss match key values.

What do you mean by 'on the go'?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Charmer SSChampion Points: 14903 More actions · Answer 4

Phil Parkin - Tuesday, August 22, 2017 10:24 AM
Charmer - Tuesday, August 22, 2017 9:55 AM
Hi Phil,
It's already redirected. But now they want to do this on the go. They want to remove look up and use script component and identify the miss match key values.
What do you mean by 'on the go'?

I mean at the time of when the data flow is executed. I have been instructed to Replace lookup with script component and find out the reason of non matched rows and process the matched rows.

Thanks,
Charmer

Phil Parkin SSC Guru Points: 246970 More actions · Answer 5

Charmer - Tuesday, August 22, 2017 10:45 AM
I mean at the time of when the data flow is executed. I have been instructed to Replace lookup with script component and find out the reason of non matched rows and process the matched rows.

OK, but, by definition, the reason for the non-matched rows is that no match was found.
What other 'reason' are you expecting to find?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Charmer SSChampion Points: 14903 More actions · Answer 6

Phil Parkin - Tuesday, August 22, 2017 11:22 AM
Charmer - Tuesday, August 22, 2017 10:45 AM
I mean at the time of when the data flow is executed. I have been instructed to Replace lookup with script component and find out the reason of non matched rows and process the matched rows.
OK, but, by definition, the reason for the non-matched rows is that no match was found.
What other 'reason' are you expecting to find?

Yeah... There are 4 key column are used in the join. So outta 4, they want to find out which key column(s) is not matched

Thanks,
Charmer

Phil Parkin SSC Guru Points: 246970 More actions · Answer 7

Charmer - Tuesday, August 22, 2017 8:56 PM
Yeah... There are 4 key column are used in the join. So outta 4, they want to find out which key column(s) is not matched

Are you saying that there is a lookup table like this

(Id, Col1, Col2, Col3, Col4)
where Id = PK
and (Col1, Col2, Col3, Col4) is unique?

And that when the lookup fails, it is because one or more of the four-part combo is missing?

Surely just knowing that ('A', 'B', 'C', 'D') is missing is enough?

Eg, if the lookup is as above and the following exist in the lookup table

('A', 'B', 'A', 'D')
('A', 'B', 'B', 'D')
('C', 'B', 'C', 'D')
('A', 'B', 'C', 'C')
('A', 'D', 'D', 'D')

What would your error message be?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel