Table load

Table load

• Stuart Ray

  For better or worse as you have found out the MergeJoin only has two inputs. To get the third table just use a 2nd mergejoin.

  FFSrc1-->Sort1-->

  FFSrc2-->Sort2-->MergeJoin1-->

  FFSrc3-->Sort3----------------------->MergeJoin2-->Dest

  I know this doesn't scale well but for 3 or 4 it works fine.

  Matt

  
  
  

• Simon T.

  I have gotten the table to load using a flat file connector > sort > merge join based on siteno. I have only been able to load 2 of the files. How would I handle a third I also have some data errors in my flat files I am going to have to figure out as I cannot get some of the pure text columns to load (descriptions). You have been a great help! using the merge join I at least have been able to load some of the data!
  
  Thank you!
  
  

• MedoMancer

  Thanks Matt! I tried using union all and it loaded the data in 3 batches like this:
  
  Flatfile1 data
  -
  -
  Flatfile 2 data
  _
  _
  Flatfile 3 data
  _
  _
  
  Instead of this:
  
  Flatfile1(column 1,2,3), Flatfile2(column 3,4,5), Flatfile3(column 6,7,8)
  
  The output table is called violations where the source flat files are called Darviol, Inv, Site.
  
  The output table looks like this with associated mapping:
  
  Darviol.Siteno >> violations.siteno (column found in all 3 files)
  Darviol.ShortDesc >> violations.violation
  Darviol.Date >> violations.occurencedate
  Darviol.towit >> violations.violationremedy
  Inv.status >> violations.status
  Site.Sitename >> violations.sitename
  
  I was reading about multi flatfile configuration manager, but cannot find it! I know how to configure a single flat file source, but do not know how to configure multiple flat files.
  
  Thanks for your help!
  
  
  

• Davey

  Can you please be more specific about loaded in sequential rows My first take, since you are loading into the same table, would be to have the 3 sources feed a UnionAll then to a destination and you are done. However, this would not guarantee order of the rows being inserted. If you had to have them inserted in a specific order (i.e. each source gets all it rows inserted in serial) then you would need to either have 3 dataflow tasks with precedence constraints between them or you could use a derived column on each source to add a column with the order (if there isn't a column already available that indicates the order) and the sort and then Merge using this order column.

  So for the first it would be 1 dataflow task that looks like

  FFSrc1 -->

  FFSrc2 --> UnionAll --> Dest

  FFSrc3 -->

  For the 2nd it would be 3 dataflow tasks with precedence constraints between them all looking like:

  FFSrc --> Dest

  For the 3rd it would also be 1 dataflow task that looks like:

  FFSrc1 --> DC1 --> Sort1 -->

  FFSrc2 --> DC2 --> Sort2 --> Merge1 -->

  FFSrc3 --> DC3 --> Sort3 -------------------> Merge2 --> Dest

  1 or 2 are the better solutions

  Thanks,

  Matt

  
  
  

• vladb

  What is the key that makes the join between the columns possible In order to connect rows between sources you would normally use a MergeJoin but the source data would need a key to tell it how to join the data together. If there isn't one then you would need to either supply a pseudo key using a script component to assign row numbers or you would need to write your own script/custom component to do the merge for you.

  Matt

  
  
  
• Table load