SSIS - How to resolve issue with multiple lookup data from source
-
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.
- sks_989 - Tuesday, October 16, 2018 8:22 PM
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Wednesday, October 17, 2018 5:48 AM
Yes that what I did it but how I can merge all 3 lookups I try to merge it allows only 2 components
- sks_989 - Wednesday, October 17, 2018 6:39 AM
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Wednesday, October 17, 2018 6:45 AM
How because i need to get that column into a destination.
- sks_989 - Wednesday, October 17, 2018 7:10 AMPhil Parkin - Wednesday, October 17, 2018 6:45 AM
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Wednesday, October 17, 2018 7:17 AMsks_989 - Wednesday, October 17, 2018 7:10 AMPhil Parkin - Wednesday, October 17, 2018 6:45 AM
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.
- sks_989 - Wednesday, October 17, 2018 7:27 AMPhil Parkin - Wednesday, October 17, 2018 7:17 AMsks_989 - Wednesday, October 17, 2018 7:10 AMPhil Parkin - Wednesday, October 17, 2018 6:45 AM
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Wednesday, October 17, 2018 8:21 AMsks_989 - Wednesday, October 17, 2018 7:27 AMPhil Parkin - Wednesday, October 17, 2018 7:17 AMsks_989 - Wednesday, October 17, 2018 7:10 AMPhil Parkin - Wednesday, October 17, 2018 6:45 AM
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.
- sks_989 - Wednesday, October 17, 2018 9:23 AM
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Wednesday, October 17, 2018 10:31 AM
I am going to put Null data into that column, It is working but it is taking more time.
- sks_989 - Wednesday, October 17, 2018 11:03 AM
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Wednesday, October 17, 2018 11:21 AM
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.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply