SSIS Conditional Split Expression and Merge Join Help

Question

SSIS Conditional Split Expression and Merge Join Help

apatel 89420

Mr or Mrs. 500

Points: 554
More actions
November 4, 2011 at 12:16 pm

#250366

Hello,
I have two data sources that i'm using 'merge join' function to merge data and doing a conditional split.
Merge Data question:
I have left db source where i'm pulling Product#, CurrentManager, PreviousManager.
I have right db source where i'm pulling tblProduct#, tblCurrentManager, tblPreviousManager. (this table is a snapshot of the same left db source table from yesterday's load information.
I'm doing Left outer join on merge join transformation. I have product # left db = product # right db. i'm bringing all fields from left db source and all fields from right db source. is this correct?
Conditional split question:
after the merge data join, i'm doing a conditional split using two expression.
1. NEW (outputname) - ISNULL(PRODUCT#) == TRUE ----- this is to insert brand new rows to the right db source if the table doesnt have the account, insert all fields.
2. CHANGED(outputname) - !ISNULL(PRODUCT#) == FALSE && (CurrentManager != tblCurrentManger || PerviousManager != tblPreviousManager) -----THIS IS WHERE I WANT HELP ON CHANGE CONDITION. How can i say look for this product# and if the product # is a match verify current and previous manager. if either of them changed, update the row.
Can someone advise?
Thanks!!!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

To me your expression for Changed looks correct. Just route that output to an update routine in an OLEDB command, or if your datasets are really large route it to a staging table and then do a set based update with an execute SQL task.

apatel 89420 Mr or Mrs. 500 Points: 554 More actions · Answer 2

That is what i'm doing. The new goes to the union all and 'changed' goes to ole db command where i say, under component properties -

sql command:

UPDATE tblproductassignment

SET CurrentRecordInd = 0

WHERE

account = ?

AND

CurrentRecordInd = 1

my package runs green, but none of the rows passes through the conditional split... it is all green but the rows just get stuck at conditional split. am i missing something on the ole db command??

thanks,

sam.dahl Hall of Fame Points: 3112 More actions · Answer 3

This is what I've used in the past:

Destinations have errors because I just opened the package outside of its environment to get a screen grab.

The scripts are just providing data flow statistics so you can ignore them.

Derivations are also irrelevant to your question.

Essentially I have new data coming in at the top, old data on the right of the MRGJ.

1. The Merge Join joins on all PK fields.

2. A Conditional Split evaluates 2 things: ISNULL(OLD_KEY) --> 'Deleted' flow, ISNULL(NEW_KEY) --> 'New' flow, otherwise (default) --> 'Existing' flow

3. A second conditional split does a field by field comparison using this logic:

D_Asset_Status_Code == S_Asset_Status_Code

&& D_Asset_Status_Name == S_Asset_Status_Name

&& D_Branch_Code == S_Branch_Code

&& D_Branch_Name == S_Branch_Name

&& D_Location_Code == S_Location_Code

&& D_Location_Name == S_Location_Name

&& D_Physical_Location_Code == S_Physical_Location_Code

&& D_Physical_Location_Name == S_Physical_Location_Name

&& (ISNULL(D_Parent_Asset_Code) ? "" : D_Parent_Asset_Code) == (ISNULL(S_Master_Asset_Code) ? "" : S_Master_Asset_Code)

^ have to check for nulls where a field is nullable.

Because all fields are checked, if they pass this criteria they are sent to the Unchanged flow.

Therefore anything else by default goes to the Changed flow.

A changed row is updated entirely rather than individual attributes.

I insert my updates into a staging table and do them as a set after the data flow.

It may not be the best example but it works, perhaps others can offer tips.

I intend to look at adding a checksum to make the change determination a lot easier.

HTH

apatel 89420 Mr or Mrs. 500 Points: 554 More actions · Answer 4

thank you. I have done pretty much what you have, three outputs. then i'm doing a union all from input 1, 2 and 3. 1 = deleted, 2 = new and 3 = changed. this is where i have trouble. I'm not putting it in staging tables. any thoughts on what fields i should use for the union all. I have two data sources at the top. One coming from the same table being loaded as a snapshot on the right and one on the left where the main table data is coming from. then i'm merging and and splitting, then doing union all. in my union all, i am able to see fields from my main table and my snapshot table data sources. i'm not sure what fields i would choose as inputs in the union all.

any thoughts?

sam.dahl Hall of Fame Points: 3112 More actions · Answer 5

I don't really understand what you are trying to do.

As far as I can tell, you have this:

SRC1 > MRGJ > CSPL > > > UALL > ?

SRC2 >^

Are you separating them into 3 flows and then combining them again in the next step? Am I misunderstanding something?

What is the purpose of the union?

Generally you'd use all fields in the union all:

UALL SRC1 SRC2

----------------

colA colA colA

colB colB colB

colC colC colC