Cross join in SSIS

Lately I’ve been working a lot more in Dynamics365 than I have onPremise, and for the most part, the online CRM offers some very cool customization options, but there are scenarios, where I really would love access to the database, when performing data transformations. The fact that we can’t simply connect to the database for simple SQL tasks, have forced me to think differently. In this specific case, I needed to perform a CROSS JOIN between two custom entities to fill a custom activity.

In this scenario, I would have a list Match entity and a Task Definition entity. Each match would then get a subset of tasks created, based on which type of match, that needs to get played. With onPrem I would create a CROSS JOIN between the two tables with the given criteria, and create the corresponding activity records based on types on the match and Task Definitions.

Without the access to the database, I had to do the same in SSIS, and after a bit of fiddling, I would a way to do it rather simple:

I have my two sources (Match and Task Definition), and I need to merge them together in a Merge Join. With a merge join, we need a join key. As the Match and Task definition doesn’t have any relation, we need to manually create that join key. We can do that with a derived column, and call the column whatever you desire – I just created an integer with the value of 1.

When you have that join key on both sources, you can send them to the Merge Join.

Remember to set it to FULL OUTER JOIN in the Join Type.

This will create a full CROSS JOIN, and you can then filter your outout with a Conditional Split.

You can then throw them all into a Union all, to get a simple data stream again. This will create the records in CRM based on a crossjoin and filters.