How to extract from multiple tables with different structure to one flat file?

Question

I have following three tables, which can be joined using the common column, key and they have different number of columns.

table1 (key, colnA-1, colnA-2, …, colnA-n)
table2 (key, colnB-1, colnB-2, …, colnB-m)
table3 (key, colnC-1, colnC-2, …, colnC-i)

I need to combine the three tables and export to a flat file like following

colnA-1, colnA-2, A, colnA-3, …, colnA-n
colnA-1, colnA-2, B, colnB-1, colnB-2, …, colnB-m
colnA-1, colnA-2, B, colnB-1, colnB-2, …, colnB-m
colnA-1, colnA-2, B, colnB-1, colnB-2, …, colnB-m
…
colnA-1, colnA-2, C, colnC-1, colnC-2, …, colnC-i
colnA-1, colnA-2, C, colnC-1, colnC-2, …, colnC-i
colnA-1, colnA-2, C, colnC-1, colnC-2, …, colnC-i
…

A means the data in rest of the line are from table1, B means the data in the rest of the line are from table2, etc.  

My first question is that is it possible to write a query to join the three tables to generate a final table with the structure like above, then I could use SSIS to export the data to a flat file from the final table? My own answer to this is maybe not, since the record from table A has different structure from the record from table B.

Another question is, since I am fairly new to create a SSIS package, my only experience using SSIS is to do import/export in SSMS, can anybody share some idea how to create an SSIS package to achieve my goal?

Answer 1

I don't really understand how you're joining the three tables. I'd need specifics to be more precise.

The basic idea is to go

SELECT colnA-1, colnA-2, 'A', …, colnA-n
FROM table1
UNION ALL
SELECT colnB-1, colnB-2, 'B', …, colnB-n
FROM table2
UNION ALL
SELECT colnC-1, colnC-2, 'C', …, colnC-n
FROM table3

If your datatypes are incompatible, you'll need to use CAST() statements to make them compatible.

When you're building the package and selecting a data source, choose an OLE DB Source. In the configuration window, instead of picking a table, choose an SQL command. Then paste your working query into SSIS.