Let's say I have two data sources in SSIS.
Table A has 10 rows and two of the columns are empty. Table B has 20 rows with two columns each.
I want to somehow join them in an ETL process in a specific way: for each row of table A, 20 rows are generated with the values for the two columns from table B.
This way 200 rows should be generated with every possible combination of the rows from tables A and B
I tried using Merge Join and Union pieces, but they won't work... Any ideas how to fix this?
Add an identical column to both the sources and assign it the same value, say 1. So all the 10 rows in table1 and 20 rows in table2 have the value of "1" for that column. When you now do a merge join, keeping the join type as full outer join, Voila!, you have your cross join. Do keep in mind, the sources need to be sorted on that column for SSIS to go ahead with the Merge join, else it would throw an error.
SELECT table1.column1, table2.column2...
FROM table1, table2
A cartesian join is a cross join in MS-SQL world.
Use cross join without a where clause
A step-by-step in SSIS for cross join: http://toddmcdermid.blogspot.be/2010/09/performing-cross-join-cartesian-product.html
Some SQL Server documentation about cross join: https://technet.microsoft.com/en-us/library/ms190690%28v=sql.105%29.aspx
