Replace column data with Lookup Transformation in SSIS

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

Hi Vikas,

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

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

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

Hi,

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

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

Select allOpen 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
    );

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

just to mention
my OLEDB source table is province and my Lookup table is fdo. Also my OLEDB COmmand is for province table

Hi,

There is no Matching condition between these two tables,

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

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.

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.

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

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

No it is not replacing the unmatched names with the province name from the lookup table

Only Matched Records will be replaced with correct Province from the FDO Table ..

How you can update unmatched records ?

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

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.

thanks a lot

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.

Yes , You can, using lookup

how to add new col in that fdo table if i want to do it via ssis.

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

ic,so we cannot directly add new column in ssis. we need to do it manually outside ssis and then insert in that col

You can,
Using Execute SQL Task write query to add column