Solved

SSIS Lookup transformation to insert data in table

Posted on 2014-07-21
23
1,301 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 15

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 15

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 15

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 15

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 15

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
 

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 15

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 15

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 15

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 15

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 15

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 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