Multiple Tables into one Text File

[kgreer]

Here is my issue.

I have to produce a text File that looks like below:
PAT, 123456,SMITH,JOHN,,1200 Main St,,Houston,TX,77074,7138889999,01011960,123456789,M
CLM, 01012010,01022010,,Jon,Brown,David,Lasso,,1234567890,,SW Hospital,340 Main,,Houston,TX,77000,34500,32301,,
PROC, 01012010,01012010,99411,,,,21,1,12,1,20000
PROC, 01022010,01022010,99411,,,,21,1,12,1,20000
INS1, AET01,AETNA,1200 BLOOMINGDALE RD,,SEATTLE,OR,92892,SMITH,JOHN,,1200 Main St,,Houston,TX,77074,7139998888,01011960,123456789,M,01,390293,,TX INST


Each row is a table.

PAT is table PAT_TABLE
CLM is table CLM_TABLE
PROC is table PROC_TABLE -- The detail information, repeats
INS1 is table INS1_TABLE

I have queries that can return the data in each table, then I do a UNION on the tables to display them. Because I am using a UNION, I have to make sure all the column match in each query. The issue is I don't need the extra columns in the text file.

So I was wondering can this be done in a SSIS package? Right now it is generating my Text File with all the extra columns.

 

[jjefferson]

I think the only way you are going to get un-matched rows like this would be to have each query concatenate the values into one varchar(max) column, and union those.

So your PAT query would build a long string with whatever values and commas are needed as a single string, CLM would do the same, etc. Then you end up with essentially one column being reported from each query, which can be unioned.