SSIS: Looking for a Cross-Join
We investigated about a solution for that… Finally my Avanade colleague Tom Lichtenstein came up with an approach weeks ago which worked perfectly… Now I finally find some time to blog about that and: well, I’m not the first one…
Did you ever want to do a Cross-Join with the Merge Join transform? There isn’t a feature like that… What you have to do is add a “dummy” column (i.e. by a Derived Column transform) in both pipelines, sort by that column and join them. That’s expensive (because of the sort) and more a hack than anything else… Perhaps you can do the Cross-Join in your SQL query of the source instead… Perhaps you can at least add the dummy column in your source queries (that works if you have two different sources you want to join) and change the sort property in the output so that SSIS things the source is already sorted by that column (so you don’t need the Sort transform anymore).
One thing you should do for sure: vote for a change in the Merge Join transform so that we’ll see a Cross-Join in the future… https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=161154
Thomas