Solved

Replace column data with Lookup Transformation in SSIS

Posted on 2014-07-23
23
1,344 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
  • 13
  • 10
23 Comments
 
LVL 14

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 14

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
 

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 14

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 14

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
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

 
LVL 14

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 14

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 14

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 14

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 14

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 14

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

760 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

18 Experts available now in Live!

Get 1:1 Help Now