CODEWITHSUNDEEP
mysqlsqlsql-serverssis
user2588812
user2588812

Reputation: 67

SSIS Derived Column for multiple finds

I want to use where clause in derived column ie

I have Table 1, Table 2 I have to compare these both and change the Name column in table 1.

Two conditions are to be satisfied 

 1) If I have name as UNK or UNKNOWN or NULL and HI_UN as 1 then it needs to be changed to UNKNOWN
 2) If I have name as UNK or UNKOWN or NULL or None and P_M has A or R then it needs to be changed to UNoccupy

how do I incorporate this in derived column logic in SSIS

Table 1

 NUM           VEH              NAME                     P_M          HI_UN
 017            1       UNK                               A             1
 037            2       DESORMEAUX, JR. MICHELLE          B             2
 043            3       UNK                               R             3
 073            4       UNKNOWN                           A             4

Table 2 

VEH           HI_UN
1               1
2               2
3               3
4               4

END RESULT:

     NUM           VEH              NAME                     P_M          HI_UN
     017            1       UNKNOWN                           A             1
     037            2       DESORMEAUX, JR. MICHELLE          B             2
     043            3       UNoccupy                          R             3
     073            4       UNKNOWN                           A             4

Upvotes: 0

Views: 1318

Answers (2)

mehmet sahin
mehmet sahin

Reputation: 812

You can use a sql execute task and use sql merge functions like that ;

http://technet.microsoft.com/en-us/library/bb510625.aspx

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

When you say you want to change the name in table 1 it sounds like you aren't actually needing a derived column but just a sql task with an update statement, even if you are wanting to use a derived column, it may be simpler to use a sql task to populate, as the expression will be unwieldy:

UPDATE a
SET a.Table1 = CASE WHEN (a.Name IN ('UNK','UNKNOWN') OR a.Name IS NULL) AND a.HI_UN = 1 THEN 'UNWNOWN'
                    WHEN (a.Name IN ('UNK','UNKNOWN','None') OR a.Name IS NULL) AND P_M IN ('A','R') THEN 'UNoccupy'
                    ELSE a.Name
               END
FROM Table1 a
JOIN Table2 b
    ON a.VEH = b.VEH
    AND a.HI_UN = b.HI_UN

Note: Assuming the sample data isn't complete and that there's some reason for using the JOIN.

A derived column transformation can only make use of one input. You could add a Merge Join and feed that into a derived column if you wanted, but again, the expression needed would be a little unwieldy, something like:

(Name == "UNK" || Name == "UNKNOWN" || ISNULL(Name)) && HI_UN == “1” ? "UNKNOWN" : ((Name == "UNK" || Name == "UNKNOWN" || ISNULL(Name)) && P_M == “A” || P_M == “A” ? "UNoccupy" : Name)

Upvotes: 1

Related Questions