Solved

SSIS Lookup transformation to insert data in table

Posted on 2014-07-21
23
1,280 Views
Last Modified: 2016-02-11
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
0
Comment
Question by:zolf
  • 13
  • 10
23 Comments
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40210900
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
 

Author Comment

by:zolf
ID: 40210923
Thanks for your comments. By I dont understand what you are trying to instruct me.Can you please elaborate
0
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40210938
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
 

Author Comment

by:zolf
ID: 40210955
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
 

Author Comment

by:zolf
ID: 40210957
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
 

Author Comment

by:zolf
ID: 40210960
I have attached the Transformation frame.please have a look and see if i am doing it correct
ee1.gif
0
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40210971
If the Matching column have same data type and have matching values then it looks perfect
0
 

Author Comment

by:zolf
ID: 40210972
I dont see that fdocity code which i select in the transformation frame
ee2.gif
0
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40210988
Delete the data viewer and add them again it will be visible.
0
 

Author Comment

by:zolf
ID: 40211001
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
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40211012
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:zolf
ID: 40211023
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
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40211026
Now you got the Lookedup column FDOCityCode in MatchedOutput.

you can now attach this to any destination table where you want its value
0
 

Author Comment

by:zolf
ID: 40211032
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
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40211135
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
 

Author Comment

by:zolf
ID: 40211136
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
 

Author Comment

by:zolf
ID: 40211141
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
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40211142
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
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40211161
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
 

Author Comment

by:zolf
ID: 40211162
please have a look at my shot. I am not sure if i am doing it correctly.please bear with me!!
ee6.gif
0
 

Author Comment

by:zolf
ID: 40211167
and this is my data flow
ee7.gif
0
 
LVL 14

Accepted Solution

by:
Vikas Garg earned 500 total points
ID: 40211170
Correct Go ahead
0
 

Author Closing Comment

by:zolf
ID: 40211179
cheers!! Got it at last with your help. Appreciate your help.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now