SSIS - How to resolve issue with multiple lookup data from source

Question

SSIS - How to resolve issue with multiple lookup data from source

sks_989

SSC Eights!

Points: 843
More actions
October 16, 2018 at 8:22 pm

#381242

I get source data from excel file.
In source data i get primary key , description , Account name , Trade Company Name , Security Group Name
In my destination i have 3 lookup table for Account name , Trade Company Name and Security Group Name.
When i am writing SSIS package i can get this 3 look up key is into destination table so i need following in destination table
Primary Key, Description, Account id , Trad company Id, Security group id.
I try to lookup transformation but i cannot add 3 lookup transformation into 1 transformation ( i don't know which transformation i can use i try union all but it not work ) from that i can put into destination table
I really appreciate any one can help me out this one.

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

sks_989 - Tuesday, October 16, 2018 8:22 PM
I get source data from excel file.
In source data i get primary key , description , Account name , Trade Company Name , Security Group Name
In my destination i have 3 lookup table for Account name , Trade Company Name and Security Group Name.
When i am writing SSIS package i can get this 3 look up key is into destination table so i need following in destination table
Primary Key, Description, Account id , Trad company Id, Security group id.
I try to lookup transformation but i cannot add 3 lookup transformation into 1 transformation ( i don't know which transformation i can use i try union all but it not work ) from that i can put into destination table
I really appreciate any one can help me out this one.

Just do three separate lookups in the same data flow. This sort of thing.

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

sks_989 SSC Eights! Points: 843 More actions · Answer 2

Phil Parkin - Wednesday, October 17, 2018 5:48 AM
sks_989 - Tuesday, October 16, 2018 8:22 PM
I get source data from excel file.
In source data i get primary key , description , Account name , Trade Company Name , Security Group Name
In my destination i have 3 lookup table for Account name , Trade Company Name and Security Group Name.
When i am writing SSIS package i can get this 3 look up key is into destination table so i need following in destination table
Primary Key, Description, Account id , Trad company Id, Security group id.
I try to lookup transformation but i cannot add 3 lookup transformation into 1 transformation ( i don't know which transformation i can use i try union all but it not work ) from that i can put into destination table
I really appreciate any one can help me out this one.
Just do three separate lookups in the same data flow. This sort of thing.

Yes that what I did it but how I can merge all 3 lookups I try to merge it allows only 2 components

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

sks_989 - Wednesday, October 17, 2018 6:39 AM
Phil Parkin - Wednesday, October 17, 2018 5:48 AM
sks_989 - Tuesday, October 16, 2018 8:22 PM
I get source data from excel file.
In source data i get primary key , description , Account name , Trade Company Name , Security Group Name
In my destination i have 3 lookup table for Account name , Trade Company Name and Security Group Name.
When i am writing SSIS package i can get this 3 look up key is into destination table so i need following in destination table
Primary Key, Description, Account id , Trad company Id, Security group id.
I try to lookup transformation but i cannot add 3 lookup transformation into 1 transformation ( i don't know which transformation i can use i try union all but it not work ) from that i can put into destination table
I really appreciate any one can help me out this one.
Just do three separate lookups in the same data flow. This sort of thing.
Yes that what I did it but how I can merge all 3 lookups I try to merge it allows only 2 components

No merging is required. Each looked-up column gets added to the data flow pipeline. After all of the lookups are complete, all of the required looked-up columns will be available for you to use.

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

sks_989 SSC Eights! Points: 843 More actions · Answer 4

Phil Parkin - Wednesday, October 17, 2018 6:45 AM
sks_989 - Wednesday, October 17, 2018 6:39 AM
Phil Parkin - Wednesday, October 17, 2018 5:48 AM
sks_989 - Tuesday, October 16, 2018 8:22 PM
I get source data from excel file.
In source data i get primary key , description , Account name , Trade Company Name , Security Group Name
In my destination i have 3 lookup table for Account name , Trade Company Name and Security Group Name.
When i am writing SSIS package i can get this 3 look up key is into destination table so i need following in destination table
Primary Key, Description, Account id , Trad company Id, Security group id.
I try to lookup transformation but i cannot add 3 lookup transformation into 1 transformation ( i don't know which transformation i can use i try union all but it not work ) from that i can put into destination table
I really appreciate any one can help me out this one.
Just do three separate lookups in the same data flow. This sort of thing.
Yes that what I did it but how I can merge all 3 lookups I try to merge it allows only 2 components
No merging is required. Each looked-up column gets added to the data flow pipeline. After all of the lookups are complete, all of the required looked-up columns will be available for you to use.

How because i need to get that column into a destination.

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

sks_989 - Wednesday, October 17, 2018 7:10 AM
Phil Parkin - Wednesday, October 17, 2018 6:45 AM
sks_989 - Wednesday, October 17, 2018 6:39 AM
Phil Parkin - Wednesday, October 17, 2018 5:48 AM
Just do three separate lookups in the same data flow. This sort of thing.
Yes that what I did it but how I can merge all 3 lookups I try to merge it allows only 2 components
No merging is required. Each looked-up column gets added to the data flow pipeline. After all of the lookups are complete, all of the required looked-up columns will be available for you to use.
How because i need to get that column into a destination.

You did not follow the link I sent you, despite saying "That's what I did" !!!!

Get rid of the multicast and perform the lookups in series, not parallel.

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

sks_989 SSC Eights! Points: 843 More actions · Answer 6

Phil Parkin - Wednesday, October 17, 2018 7:17 AM
sks_989 - Wednesday, October 17, 2018 7:10 AM
Phil Parkin - Wednesday, October 17, 2018 6:45 AM
sks_989 - Wednesday, October 17, 2018 6:39 AM
Phil Parkin - Wednesday, October 17, 2018 5:48 AM
Just do three separate lookups in the same data flow. This sort of thing.
Yes that what I did it but how I can merge all 3 lookups I try to merge it allows only 2 components
No merging is required. Each looked-up column gets added to the data flow pipeline. After all of the lookups are complete, all of the required looked-up columns will be available for you to use.
How because i need to get that column into a destination.
You did not follow the link I sent you, despite saying "That's what I did" !!!!
Get rid of the multicast and perform the lookups in series, not parallel.

Sorry, I have not seen your link. I need to do parallel because some time data match some time they don't have data. I need to do multicast because of that. I saw your link because you have match data then you go further. Let me think more if i can do that way but any your suggestion greatly appreciated.

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

sks_989 - Wednesday, October 17, 2018 7:27 AM
Phil Parkin - Wednesday, October 17, 2018 7:17 AM
sks_989 - Wednesday, October 17, 2018 7:10 AM
Phil Parkin - Wednesday, October 17, 2018 6:45 AM
sks_989 - Wednesday, October 17, 2018 6:39 AM
Phil Parkin - Wednesday, October 17, 2018 5:48 AM
Just do three separate lookups in the same data flow. This sort of thing.
Yes that what I did it but how I can merge all 3 lookups I try to merge it allows only 2 components
No merging is required. Each looked-up column gets added to the data flow pipeline. After all of the lookups are complete, all of the required looked-up columns will be available for you to use.
How because i need to get that column into a destination.
You did not follow the link I sent you, despite saying "That's what I did" !!!!
Get rid of the multicast and perform the lookups in series, not parallel.
Sorry, I have not seen your link. I need to do parallel because some time data match some time they don't have data. I need to do multicast because of that. I saw your link because you have match data then you go further. Let me think more if i can do that way but any your suggestion greatly appreciated.

Remember that you can control what happens in the event that a match is not found, eg, by setting the 'Ignore Failure' property on the lookup.

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

sks_989 SSC Eights! Points: 843 More actions · Answer 8

Phil Parkin - Wednesday, October 17, 2018 8:21 AM
sks_989 - Wednesday, October 17, 2018 7:27 AM
Phil Parkin - Wednesday, October 17, 2018 7:17 AM
sks_989 - Wednesday, October 17, 2018 7:10 AM
Phil Parkin - Wednesday, October 17, 2018 6:45 AM
sks_989 - Wednesday, October 17, 2018 6:39 AM
Phil Parkin - Wednesday, October 17, 2018 5:48 AM
Just do three separate lookups in the same data flow. This sort of thing.
Yes that what I did it but how I can merge all 3 lookups I try to merge it allows only 2 components
No merging is required. Each looked-up column gets added to the data flow pipeline. After all of the lookups are complete, all of the required looked-up columns will be available for you to use.
How because i need to get that column into a destination.
You did not follow the link I sent you, despite saying "That's what I did" !!!!
Get rid of the multicast and perform the lookups in series, not parallel.
Sorry, I have not seen your link. I need to do parallel because some time data match some time they don't have data. I need to do multicast because of that. I saw your link because you have match data then you go further. Let me think more if i can do that way but any your suggestion greatly appreciated.
Remember that you can control what happens in the event that a match is not found, eg, by setting the 'Ignore Failure' property on the lookup.

yep I am doing redirect rows to no match output and check with a match and no match it is a long process but it will be work. Thanks, I will let you know when it is done.

Phil Parkin SSC Guru Points: 246988 More actions · Answer 9

sks_989 - Wednesday, October 17, 2018 9:23 AM
yep I am doing redirect rows to no match output and check with a match and no match it is a long process but it will be work. Thanks, I will let you know when it is done.

So ... what do you want to happen if a lookup fails?
Let's say that your AccountId lookup does not find a match. What would you want to put into the AccountId column in this case?

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

sks_989 SSC Eights! Points: 843 More actions · Answer 10

Phil Parkin - Wednesday, October 17, 2018 10:31 AM
sks_989 - Wednesday, October 17, 2018 9:23 AM
yep I am doing redirect rows to no match output and check with a match and no match it is a long process but it will be work. Thanks, I will let you know when it is done.
So ... what do you want to happen if a lookup fails?
Let's say that your AccountId lookup does not find a match. What would you want to put into the AccountId column in this case?

I am going to put Null data into that column, It is working but it is taking more time.

Phil Parkin SSC Guru Points: 246988 More actions · Answer 11

sks_989 - Wednesday, October 17, 2018 11:03 AM
Phil Parkin - Wednesday, October 17, 2018 10:31 AM
sks_989 - Wednesday, October 17, 2018 9:23 AM
yep I am doing redirect rows to no match output and check with a match and no match it is a long process but it will be work. Thanks, I will let you know when it is done.
So ... what do you want to happen if a lookup fails?
Let's say that your AccountId lookup does not find a match. What would you want to put into the AccountId column in this case?
I am going to put Null data into that column, It is working but it is taking more time.

So ... instead of using redirect to no match output, consider using ignore failure. That way, there is only one path and your missed lookups will indeed be NULL.
Another suggestion is to set the failed lookup Ids to -1 (or some other unlikely value) and to add -1 to your associated dim tables as 'Unknown' (or whatever makes sense).

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

sks_989 SSC Eights! Points: 843 More actions · Answer 12

Phil Parkin - Wednesday, October 17, 2018 11:21 AM
sks_989 - Wednesday, October 17, 2018 11:03 AM
Phil Parkin - Wednesday, October 17, 2018 10:31 AM
sks_989 - Wednesday, October 17, 2018 9:23 AM
yep I am doing redirect rows to no match output and check with a match and no match it is a long process but it will be work. Thanks, I will let you know when it is done.
So ... what do you want to happen if a lookup fails?
Let's say that your AccountId lookup does not find a match. What would you want to put into the AccountId column in this case?
I am going to put Null data into that column, It is working but it is taking more time.
So ... instead of using redirect to no match output, consider using ignore failure. That way, there is only one path and your missed lookups will indeed be NULL.
Another suggestion is to set the failed lookup Ids to -1 (or some other unlikely value) and to add -1 to your associated dim tables as 'Unknown' (or whatever makes sense).

Yes, it worked but it was taking too much time to process. What I did alternative solution is to create a temp table into a destination and insert a record for 3 columns with primary key and then update destination table with the primary key using a temp table. Thanks for all your help I learn something new today. I appreciated all your help.