How to use SSIS to export data from multiple tables into one flat file?

Question

I have 2 tables with different number of columns, and I need to export the data using SSIS to a text file. For example, I have customer table, tblCustomers; order table, tblOrders

tblCustomers (id, name, address, state, zip)
id  name    address state   zip’
100 custA   address1    NY  12345
99  custB   address2    FL  54321

and

tblOrders(id, cust_id, name, quantity, total, date)
id  cust_id     name    quantity    total   date
1   100     candy   10      100.00  04/01/2014
2   99      veg 1       2.00    04/01/2014
3   99      fruit   2       0.99    04/01/2014
4   100     veg 1       3.99    04/05/2014

The result file would be as following

“custA”, “100”, “recordtypeA”, “address1”, “NY”, “12345”
“custA”, “100”, “recordtypeB”, “candy”, “10”, “100.00”, “04/01/2014”
“custA”, “100”, “recordtypeB”, “veg”, “1”, “3.99”, “04/05/2014”
“custB”, “99”, “recordtypeA”, “address2”, “FL”, “54321”
“custB”, “99”, “recordtypeB”, “veg”, “1”, “2.00”, “04/01/2014”
“custB”, “99”, “recordtypeB”, “fruit”, “2”, “0.99”, “04/01/2014”

Can anyone please guild me as how to do this?

Answer 1

I presume you meant "guide", not "guild" - I hope your typing is more careful when you code?

I would create a Data Flow Task in an SSIS package. In that I would first add an OLE DB Source and point it at tblOrders. Then I would add a Lookup to add the data from tblCustomers, by matching tblOrders.Cust_id to tblCustomers.id.

Answer 2

I would use a SQL Query that joins the tables, and sets up the data, use that as a source and export that.

Note that the first row has 6 columns and the second one has 7. It's generally difficult (well not as easy as a standard file) to import these types of header/detail files. How is this file being used once created? If it needs to be imported somewhere you'd be better of just joining the data up and having 10 columns, or exporting them seperately.