February 8, 2016 at 4:42 am
Hi
I'm using SSIS 2008 R2. I have two datasets on which I want to do a full outer join based on a match string, no problem there, I've set up a Merge Join. Where a match is found I want to output a non key field from DataSet A if there is one, or the corresponding field in DataSet B if the value is null in DataSet A.
Basically, I'm looking for the SSIS equivalent of something like this:-
Select isnull(T1.Field1, T2.Field2)
From Table1 T1
Full Outer Join Table2 T2
on T1.KeyField = T2.KeyField
At present I'm including the columns from both sides of the join and then using a subsequent Derived Column task to coerce them into single values. This feels a bit unwieldy, though, and I can't help thinking I'm missing something obvious.
February 8, 2016 at 5:44 am
FunkyDexter (2/8/2016)
HiI'm using SSIS 2008 R2. I have two datasets on which I want to do a full outer join based on a match string, no problem there, I've set up a Merge Join. Where a match is found I want to output a non key field from DataSet A if there is one, or the corresponding field in DataSet B if the value is null in DataSet A.
Basically, I'm looking for the SSIS equivalent of something like this:-
Select isnull(T1.Field1, T2.Field2)From Table1 T1
Full Outer Join Table2 T2
on T1.KeyField = T2.KeyField
At present I'm including the columns from both sides of the join and then using a subsequent Derived Column task to coerce them into single values. This feels a bit unwieldy, though, and I can't help thinking I'm missing something obvious.
If you want to keep this matching process wholly within SSIS, it sounds like you're doing the right thing. If performance is an issue, you might want to consider re-architecting the solution such that the join is done in T-SQL.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 8, 2016 at 6:53 am
If you want to keep this matching process wholly within SSIS
It's a strong driver but not an absolute imperative. I want to keep as much in SSIS as possible but a few bits of sql won't hurt if there's a bottleneck.
Knowing that my approach is broadly right from a SSIS point of view is good to hear, though. I'm no expert at SSIS so it's hard to know what I don't know.
February 8, 2016 at 7:04 am
FunkyDexter (2/8/2016)
If you want to keep this matching process wholly within SSIS
It's a strong driver but not an absolute imperative. I want to keep as much in SSIS as possible but a few bits of sql won't hurt if there's a bottleneck.
Knowing that my approach is broadly right from a SSIS point of view is good to hear, though. I'm no expert at SSIS so it's hard to know what I don't know.
I mentioned performance purely because the merge join component in SSIS is semi-blocking and join performance in the DB engine usually blows it away.
If you do not have huge volumes of data to match and the process appears to work OK, go with it.
Some quick background reading here.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 8, 2016 at 7:32 am
Thanks for the info. That link should prove to be a pretty good guide if I need to performance tune some of this later.
Maintainability is the trump card on this because it's going to get handed over to a non sql guy down the line. Hence the desire to keep as much in SSIS as possible. He'll muddle through some SQL if he has to but the easier and more "GUIfied" I can make it for him the better.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply