Replace column data with Lookup Transformation in SSIS

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
zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
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
zolfAuthor Commented:
Hi Vikas,

But doing that will it replace those non matching records. i am confused!!.please can you explain the reason
0
Vikas GargBusiness Intelligence DeveloperCommented:
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
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.

zolfAuthor Commented:
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
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Can you give me source and destination table detail with some sample data so that I can give you accurate solution ?
0
zolfAuthor Commented:
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
zolfAuthor Commented:
just to mention
my OLEDB source table is province and my Lookup table is fdo. Also my OLEDB COmmand is for province table
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

There is no Matching condition between these two tables,

How SQL come to know that it has to replace EFG with EFF ?
0
zolfAuthor Commented:
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
zolfAuthor Commented:
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
zolfAuthor Commented:
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
Vikas GargBusiness Intelligence DeveloperCommented:
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
zolfAuthor Commented:
No it is not replacing the unmatched names with the province name from the lookup table
0
Vikas GargBusiness Intelligence DeveloperCommented:
Only Matched Records will be replaced with correct Province from the FDO Table ..

How you can update unmatched records ?

Its not feasible................
0
zolfAuthor Commented:
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
Vikas GargBusiness Intelligence DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
thanks a lot
0
zolfAuthor Commented:
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
Vikas GargBusiness Intelligence DeveloperCommented:
Yes , You can, using lookup
0
zolfAuthor Commented:
how to add new col in that fdo table if i want to do it via ssis.
0
Vikas GargBusiness Intelligence DeveloperCommented:
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
zolfAuthor Commented:
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
Vikas GargBusiness Intelligence DeveloperCommented:
You can,
Using Execute SQL Task write query to add column
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

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.