0

I have created a derived column in my dataflow that is the simple concatenation of two columns. I have done this to two separate data sources. I then want to perform a merge join with my newly derived column as the outer join key. However, it doesn't seem like it is possible to accomplish this? Does anyone have experience with something like this?

The issue stems from the fact that I am unable to set a "Sort Key Position" to my newly created column as this is specified at the source. It is not possible to set this at the Derived Column transformation.

enter image description here

enter image description here

MaxB
  • 428
  • 1
  • 8
  • 24

1 Answers1

1

You would need to add a sort component between the merge join and the Derived Column sorting on the column(s) introduced in the Derived Column components.

While Excel is definitely going to need the derived column + Sort to make this work, I have not run into a situation where I could express an idea in the SSIS Expression language that I could not also do the same in TSQL. If you can, it will simplify your package as well as speed up the execution time.

Also, it's been my experience a Lookup Component is most often the tool people want compared to a Merge Join. If I'm augmenting an existing row, Lookup. If I need to be able to have 1 row generate 0 to many rows, then a merge join may be appropriate.

I know you had a lookup question earlier. Excel can act as a Lookup source if you use the Cache Connection Manager Excel Source as Lookup Transformation Connection

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Thank you for the response. My goal here is to join two tables on a common column. Any table that is explicitly not null on the left data source and IS null on the right data source will be flagged. – MaxB Oct 12 '20 at 19:49