Cartesian product in SSIS?
I have two tables(Table1 and Table2) in a SQL Server database and I need to create a third table (Table3) that contains all the columns from the two tables and of course their data. Each row of Table1 is associated with each row of Table2. This operation is a cartesian product but apparently it is not available in SSIS. May anybody know how to do it? I read in the Internet that using the cartesian product is not a good practice in terms of performance but Table2 has just one row, meaning that Table3 will have as many rows as Table1
July 7th, 2011 9:18am

In your Data Flow Source, you could perform the join using a CROSS JOIN in TSQL, then pump it out to your Data Flow.Peter Carter http://sqlserverdownanddirty.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
July 7th, 2011 9:26am

use Merge Join see the Todd's Post http://www.bimonkey.com/2010/10/the-merge-join-transformation/ Cheers, Shailesh
July 7th, 2011 9:27am

As you said , You have two tables and they have in SQL Server, WHY don't you use Cross Join in your query( you will write your query in OLEDB Source). Check this artical how to do Cross Join in SSIS http://sqlserverpedia.com/blog/sql-server-bloggers/performing-a-cross-join-cartesian-product-in-ssis/ Thankshttp://sqlage.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
July 7th, 2011 9:28am

Such a long procedure to perform a simple cartesian product. SSIS must really improve from this point of view. I already encountered two situations that needed such operator. Thanks everybody for the answers I just marked the one that provided me the best link.
July 7th, 2011 9:57am

This capability does exist in two indirect ways. Not too hard to implement. Please see Todd McDermid's post http://sqlserverpedia.com/blog/sql-server-bloggers/performing-a-cross-join-cartesian-product-in-ssis/ Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
July 7th, 2011 10:30am

Such a long procedure to perform a simple cartesian product. SSIS must really improve from this point of view. Here's a speculative answer as to why there is no Cross Join component in SSIS... Think about what you're asking for. A cartesian product potentially requires masses of memory (and would even more so for SSIS given that we are typically processing large data volumes). Cartesian products (i.e. Cross Joins) work in SQL Server cos we have this helpful little thing called [tempdb] - there is no analogous construct in SSIS save for disk spooling. SSIS is deliberately not optimised for mass data spooling - it is optimised for processing small batches of data at a time before passing them off to some destination. This is why there is no Cross Join component. the capabilities to achieve a cross join exist (see provided links) but they want you to be deliberate about it - they're not going to give you enough rope to hang yourself with by providing a component that could bring the machine of an unwitting developer to its knees.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
July 7th, 2011 10:39am

P.S. I suspect this is also the reason for there being no Nested Loop Join component.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
Free Windows Admin Tool Kit Click here and download it now
July 7th, 2011 10:41am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics