Need to generate a flat file from different tables having different number of columns

• murali.velaga

  Grasshopper

  Points: 13

  More actions

  May 10, 2011 at 7:44 pm

  #145508

  Hi,

  I have 3 different tables with different structure

  table1 (ID as int, Name as nvarchar(20))

  table2 (ID as int, address1ID as int, property1 as nvarchar(20),property2 as nvarchar(20))

  table3 (ID as int, subID as int, subName as nvarchar(20))

  Now, I have to generate a flat file as below:

  ID Name (this row from table1)

  ID addressID Property1 property2 (this row from table2)

  ID subID subName (this row from table3)

  The above 3 rows should repeat for every ID.

  Please suggest me any approach how to generate this file.

  Thanx in Advance..

  Murali

• Koen Verbeeck

  SSC Guru

  Points: 259197

  More actions

  May 11, 2011 at 2:33 am

  #1322799

  I would use a single OLE DB source that selects the data from the 3 tables and joins them on ID.

  Then I would use a script component as a destination. In the script, you can split out one row into the 3 needed rows and write them to a flat file.

  Question: is there a good reason to construct the flat file that way? It sure isn't straightforward...

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP