Solved

Replace column data with Lookup Transformation in SSIS

Posted on 2014-07-23
23
1,562 Views
Last Modified: 2016-02-11
Hello there,

I am trying to compare two tables and replace the wrong spelled words to the correct one from the lookup package. But when I execute the package those words get replaced with Null in the table. Infact It should replace with the word from the lookup table. Please see the shot I have attached to get an idea to what I am trying to do.please help.
s.gif
s1.gif
s2.gif
s3.gif
0
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
23 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213632
Hi,

I think you have to just replace you Oledb command from Non Matching output to the Matching Output of the Lookup output and That's it ..
0
 

Author Comment

by:zolf
ID: 40213640
Hi Vikas,

But doing that will it replace those non matching records. i am confused!!.please can you explain the reason
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213642
No,

Non match records will remain as it is since those are filtered out and will not appear in the update statement in the Oled command
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:zolf
ID: 40213644
I mean moving the oledb to matching output will replace the matching records which I dont want. I wan to replace the non matching names with the lookup names
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213647
Hi,

Can you give me source and destination table detail with some sample data so that I can give you accurate solution ?
0
 

Author Comment

by:zolf
ID: 40213661
This table province is my actual table which i want to use in production and the table fdo i got from excel into my db. But it has the correct names which I want to use in my province table.Now below is the table structures.

CREATE TABLE
    province
    (
        id BIGINT NOT NULL,
        provincename NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        capital NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        code INT,
        PRIMARY KEY (id)
    );

Open in new window


CREATE TABLE
    fdo
    (
        Province NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        County NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        District NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        City NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        FDOCityCode INT,
        ProvinceTaxCode INT,
        CityTaxCode INT,
        TelCode INT
    );

Open in new window



Below is the sampel data

PROVINCE TABLE----

id      provincename      capital      code
1        ABC                        abc           2
2        EFG                        egf            23
3        WER                        wer           34
4        BBB                        bbb           677
5        ZZZ                        zzz           89
6        QQQ                       qqq           800


FDO TABLE -----

Province      County      District      City             FDOCityCode          ProvinceTaxCode        CityTaxCode           TelCode
ABC          dfd            trtrt         jhjh         555                       66666                      454                   555
EFF          asa            thn         bfss         3                      11                        333                               777
QQ          dfew           mju        nuo         8                       321                     5612                           908
ZZZ          jku            qbh        loo         9                      7890                      1009                            335


Now take this example where i have make the row in BOLD as the wrong province name in the province table.instead of EFG it should be EFF.

hope I made myself clear. the reason I am not giving my actual data is that my data is in RTL and it could be difficult for you to understand
0
 

Author Comment

by:zolf
ID: 40213664
just to mention
my OLEDB source table is province and my Lookup table is fdo. Also my OLEDB COmmand is for province table
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213669
Hi,

There is no Matching condition between these two tables,

How SQL come to know that it has to replace EFG with EFF ?
0
 

Author Comment

by:zolf
ID: 40213670
one more thing i noticed is tha the provinceTaxCode in the fdo table is same as the code col in the province table. i did not reflect that in the e.g.
0
 

Author Comment

by:zolf
ID: 40213671
How SQL come to know that it has to replace EFG with EFF ?
Please see my note i posted just now. i just noticed myself i made a mistake in the e.g.
0
 

Author Comment

by:zolf
ID: 40213673
PROVINCE TABLE----

id      provincename      capital      code
1        ABC                        abc           2
2        EFG                        egf            23
3        WER                        wer           34
4        BBB                        bbb           677
5        ZZZ                        zzz           89
6        QQQ                       qqq           800


FDO TABLE -----

Province      County      District      City             FDOCityCode          ProvinceTaxCode        CityTaxCode           TelCode
ABC          dfd            trtrt         jhjh         555                       2                      454                   555
EFF          asa            thn         bfss         3                      23                        333                               777
QQQ          dfew           mju        nuo         8                       800                     5612                           908
ZZZ          jku            qbh        loo         9                      89                      1009                            335
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213679
My Friend,

That is what I was telling you from the beginning that you have to move the OLEDB command from Non Match to Matched site so after matching the Code and provinceTaxCode  in Lookup the Province column will be ticked and taken in the source and then run the update Query like this

Update province set Province  = ? where id = ?
and map the Province (Lookup column) and id to it
0
 

Author Comment

by:zolf
ID: 40213690
No it is not replacing the unmatched names with the province name from the lookup table
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213693
Only Matched Records will be replaced with correct Province from the FDO Table ..

How you can update unmatched records ?

Its not feasible................
0
 

Author Comment

by:zolf
ID: 40213696
Appreciate your feedbacks!!

so is there some way to check the province name and code and if the province name is not matching but the code is matching then replace the province name with the lookup province name.
0
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 500 total points
ID: 40213700
In Lookup Match code from Province Table with ProvinceTaxCode from FDO and take Province Column as output of the Lookup now in the Matched output place OledB Command

Update province set provincename   = ? where id = ?
and map the provincename    with looked up collumn  ProvinceTaxCode and id is the id.

This will do it
0
 

Author Closing Comment

by:zolf
ID: 40213703
thanks a lot
0
 

Author Comment

by:zolf
ID: 40213706
one question, related to those 2 tables. Can i insert the id column of the province table into the fdo table matching the province name. now that I have both the column with the same name.
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213749
Yes , You can, using lookup
0
 

Author Comment

by:zolf
ID: 40213756
how to add new col in that fdo table if i want to do it via ssis.
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213761
Add Numeric column to table
Alter Table FDO add ID int

then using lookup and oledb update command
update the FDO table with ID from Province table
0
 

Author Comment

by:zolf
ID: 40213764
ic,so we cannot directly add new column in ssis. we need to do it manually outside ssis and then insert in that col
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40213775
You can,
Using Execute SQL Task write query to add column
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

626 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