locked
Split Row's Columns Across Multiple Tables RRS feed

  • Question

  • Hello,

    Hoping someone here can help. Perhaps I'm missing something obvious, but I'm surprised not to see a data flow task in SSIS for splitting *columns* to different destinations. I see the Conditional Split task can be used to route a *row* one way or another, but what about columns of a single row?

    As a simple and somewhat contrived example, let's say I have a row with twelve fields and I'm importing the row into a normalized data structure. There are three target tables with a 1-to-1 relationship (that is, logically they are one table, but physically they are three tables, with one of them considered the "primary" table), and the twelve input fields can be mapped to four columns in each of the three tables.

    How do I "split" the columns? The best way I can see is to Multicast the row to three different OLE-DB Destinations, each of which inserts to one of the three target tables, only grabbing the four fields needed from the input row.

    Or should I feed the row through three successive OLE-DB Command tasks, each one inserting into the appropriate table? This would offer the advantage, theoretically, of allowing me to grab the identity-based surrogate primary key from the first of the three inserts in order to enable the two subsequent inserts.

    Thoughts?

    Thanks in advance,
    Dan


    Tuesday, February 7, 2006 6:18 PM

Answers

  • I think you got it - use Mutlicast. Then use OLE DB destination or OLE DB Command as it suits your requirements.

    Tuesday, February 7, 2006 6:56 PM
  • Dan,

    Multicasts are the way to go.

    The pipeline is set up to process rows. Think of each buffer as a chunk of memory that has a series of operations (i.e. the components) applied to it rather memory moving somewhere and it may help to explain why there is no sense in columns being "split" as you suggest.

    -Jamie

     

    Tuesday, February 7, 2006 7:19 PM

All replies

  • I think you got it - use Mutlicast. Then use OLE DB destination or OLE DB Command as it suits your requirements.

    Tuesday, February 7, 2006 6:56 PM
  • Dan,

    Multicasts are the way to go.

    The pipeline is set up to process rows. Think of each buffer as a chunk of memory that has a series of operations (i.e. the components) applied to it rather memory moving somewhere and it may help to explain why there is no sense in columns being "split" as you suggest.

    -Jamie

     

    Tuesday, February 7, 2006 7:19 PM
  • A thought to add to Jamie's comments: it would seem a useful concept to be able to branch the data flow by splitting a row and making new rows. A "split" would not need to mean an exact break-up of "this column goes this way" and "that column goes the other way"--some columns could be configured to be split multiple ways. I guess the idea would be a hybrid of a column-based Conditional Split and a Multicast--that is, conditionally multicast and split and choose which columns go each direction. I don't know...maybe that's crazy.
    Tuesday, February 7, 2006 10:33 PM