SSIS Lookup transformation to insert data in table

Hello there,

I have created a lookup transformation to search 2 table col for matching data and then insert another column data from matching data into the other table. My problem i show can i insert the matching data into that table i have shown in the shot.

cheers
Zolf
ee.gif
zolfAsked:
Who is Participating?
 
Vikas GargBusiness Intelligence DeveloperCommented:
Correct Go ahead
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You will get additional column from the table from which you have looked up the source and in the match output you will get the source columns along with the column wanted from the looked up table.

Now you can take Destination and map the lookedup column to the desired table column in the destination if they have same data type and if does not have same data type then you need to cast it to match the data type at source and destination
0
 
zolfAuthor Commented:
Thanks for your comments. By I dont understand what you are trying to instruct me.Can you please elaborate
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vikas GargBusiness Intelligence DeveloperCommented:
When you will do lookup you can tick the column which you want from the looked up table.

for example in your image you will lookup cityname from table1 to city of table 2 and select fdocity code from table 2 as looked up column so now in the match output you will get all the columns from table 1 and fdocity code from table 2.
So now your source has one more column which you can map to other tables column in destination
0
 
zolfAuthor Commented:
thanks,got it now. do i need to add any more package to insert the col into the new table.i dont get this
0
 
zolfAuthor Commented:
by the way does it make a difference which table I select as source and which table I select in the lookup or it makes no difference......please let me know
0
 
zolfAuthor Commented:
I have attached the Transformation frame.please have a look and see if i am doing it correct
ee1.gif
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
If the Matching column have same data type and have matching values then it looks perfect
0
 
zolfAuthor Commented:
I dont see that fdocity code which i select in the transformation frame
ee2.gif
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Delete the data viewer and add them again it will be visible.
0
 
zolfAuthor Commented:
thanks doing that did show me that column. but in my db table I dont see the the fdo city code in that fdocity col. What am i missing now.
also please answer this question of mine
by the way does it make a difference which table I select as source and which table I select in the lookup or it makes no difference......please let me know
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Source table should be the table from which you want all columns and lookup  table should be from which you want only selected column after lookup match.

And I don't see what is now your problem.
If the column now appear in the match output you just need to map it to the destination column that's it
0
 
zolfAuthor Commented:
thanks a lot for your feedbacks.

can you please elaborate on this

>>If the column now appear in the match output you just need to map it to the destination column that's it
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Now you got the Lookedup column FDOCityCode in MatchedOutput.

you can now attach this to any destination table where you want its value
0
 
zolfAuthor Commented:
I have attached my design but when i look in the city table the fdocity col is empty and is not reflecting the matching cols data.please help me.
ee3.gif
ee4.gif
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
My Dear Friend,

In the lookup match output you have to add one SQL Destination and with Table Same as Source and map the column from the source match output to this destination table ..

Is it Clear now ?
0
 
zolfAuthor Commented:
i get this error after I added a OLE DB destination pointing to that city table. see shot

Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK__city__3213E83F2D27B809'. Cannot insert duplicate key in object 'dbo.city'.".
ee5.gif
0
 
zolfAuthor Commented:
I need to somehow tell it to add the fdocode for the matching ids only....can you help me please...i am very near to acheiving my goal.if you help
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Ok,

Replace Oledb destination with OLEDB Command and Write update query

Like Update dbo.city set fdocode = ? where id = ?

and Map FDOCitycode to first parameter and id to second parameter
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
If  Updating column in dbo.city table is the only requirement then I think you don't need look up also

In Execute SQL Task Just write this query

UPDATE dbo.city
Set dbo.city.fdocode = b.FDOCityCode
FROM dbo.city a INNER JOIN Table2 b on a.cituname = b.city and a.citycode = b.Citytextcode

Open in new window

0
 
zolfAuthor Commented:
please have a look at my shot. I am not sure if i am doing it correctly.please bear with me!!
ee6.gif
0
 
zolfAuthor Commented:
and this is my data flow
ee7.gif
0
 
zolfAuthor Commented:
cheers!! Got it at last with your help. Appreciate your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.