title: SSIS: from XML file to multiple tables
body: <p>So I just started diggin SSIS today, so don't hate too much if there is something obvious I am missing.</p>
  
  <p>So I have an XML file (from a third party)</p>
  
  <pre><code>&lt;root&gt;
      &lt;foo&gt;
          &lt;fooId&gt;12345&lt;/fooId&gt;
          &lt;name&gt;FOO&lt;/name&gt;
          &lt;bars&gt;
              &lt;bar&gt;BAR 1&lt;/bar&gt;
              &lt;bar&gt;BAR 2&lt;/bar&gt;
              [...]
          &lt;/bars&gt;
      &lt;/foo&gt;
      [...]
  &lt;/root&gt;
  </code></pre>
  
  <p>and corresponding tables in my DB:<br>
  Foo with fields (FooID, Name)<br>
  Bar with fields (BarID (identity PK), FooID, Name)  </p>
  
  <p>So basically Bar is like a set of attributes for Foo.  </p>
  
  <p>So I add an XML source that points to that file and it produces 3 different datasets (<code>foo</code>, <code>bars</code>, <code>bar</code>). Problem is that <code>bar</code> set contains <code>bar</code>'s value + some autogenerated ID, which is not very useful. The only way I see from here to get a <code>bar</code> set with <code>bar</code> value and <code>fooId</code> is by sorting and merging-joining those sets, which seems rather odd and probably gonna brutally murder performance (we talking hundreds of K's of foo here).</p>
  
  <p>Question is: how to do this properly?</p>