merge tranformation in ssis

Question

Post reply

merge tranformation in ssis

h.singh10

Right there with Babe

Points: 731
More actions
January 23, 2012 at 3:23 pm

#263236

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

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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

h.singh10 Right there with Babe Points: 731 More actions · Answer 2

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?

kramaswamy SSCoach Points: 18181 More actions · Answer 3

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

Table 2

A 2 3

Merge transform will result in

Table

A B C

A 2 3

Merge join transform will result in

Table

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.