locked
Cartesian product in SSIS? RRS feed

  • Question

  • 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

    Thursday, July 7, 2011 12:59 PM

Answers

All replies

  • 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/
    Thursday, July 7, 2011 1:07 PM
  • 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/

    Thanks


    http://sqlage.blogspot.com/
    • Marked as answer by Trinakriae Thursday, July 7, 2011 1:35 PM
    Thursday, July 7, 2011 1:08 PM
  • 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.

     


    Thursday, July 7, 2011 1:37 PM
  • 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
    By: TwitterButtons.com

    Thursday, July 7, 2011 2:10 PM
  • 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
    Jamie Thomson
    Thursday, July 7, 2011 2:19 PM
  • 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
    Jamie Thomson
    Thursday, July 7, 2011 2:22 PM