merge tranformation in ssis

  • Hi

    I am having two tables :

    Table 1

    ID NAME S-CODE

    10 XYZ 1

    20 ABC 2

    Table 2:

    ID TEST INDICATOR

    10 QA N

    10 BP Y

    20 SC N

    20 UT Y

    I am using a merge transformation and loading the data in the flat file.

    The final file looks exacltly what i was looking for (i am sorting on id before merge)

    10 XYZ 1

    10 QA N

    10 BP Y

    20 ABC 2

    20 SC N

    20 UT Y

    But now i have a requirement to create flat file. When the S-code is 1, then from the Table 2, only those records should be loaded in the destination which has Indicator = 'Y'. So i am expecting the file should look like:

    10 XYZ 1

    10 BP Y

    20 ABC 2

    20 SC N

    .20 UT Y

    So the record (10 QA N) should not be loaded because it has corresponding 'N' in INDICATOR column in table 2.

    Please suggest how to proceed to create such a file with above mentioned requirement.

    Thanks

  • If you use the MERGE JOIN, you keep the columns of both tables.

    Then you can easily test your condition in a conditional split:

    (s-code == 1) && (indicator == N)

    Don't assign the output of this condition and you got rid of those rows.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Actually i am using merge and i designed the whole package. I just want to add this condition.

    Can i still use your suggestion if i continue to use merge instead of merge join?

  • EDIT ***

    Sorry, misread your post.

    The Merge transform requires both sets of data to have the same columns. So no, you can't do what you're trying to do with the Merge transform.

    EX:

    Table 1

    A B C

    A B C

    Table 2

    A 2 3

    A 2 3

    Merge transform will result in

    Table

    A B C

    A B C

    A 2 3

    A 2 3

    Merge join transform will result in

    Table

    A B C 2 3

    A B C 2 3

    You'll need to use the Merge Join transform to do what you're trying.

    Or, if you know the rows will always be a 1-1 type match, you could use the Lookup transform. It's better, but it doesn't work if there's multiple rows returned for each lookup.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply