Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Replace column data with Lookup Transformation in SSIS

Posted on 2014-07-23
23
Medium Priority
?
1,673 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 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

927 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