SSIS - Export Master-Detail data to a single flat file that contains both master and detail row

Question

I have DB with a master table with its detail table, let's say

Master (ID, MField1)
Det    (ID, IDMaster, DetField1)

and I need to export a flat file with fixed column size, with both master rows and detail rows, here's below the records' structure:

Master Record:

Position    Desc        Length    Default
 1           RecordType  1         'M'
 2           ID          10
 3           MField1     10

Detail Record:

Position    Desc        Length    Default
 1           RecordType  1         'D'
 2           ID          10
 3           MasterID    10
 4           MField1     10

For example, if I had 2 row in Master (1,'MFieldVal1'),(2,'MFieldVal2') and 3 rows in Det (1,1,'DFieldVal1'),(2,1,'DFieldVal2'),(3,2,'DFieldVal3')

I'd export a file like this:

M         1MFieldVal1
D         1         1DFieldVal1
D         2         1DFieldVal2
M         2MFieldVal2
D         3         2DFieldVal3

So far I built a view which joins Master and Det, and I used it as the source of my dataflow, but I don't know how to create the header row before its details in the exported file.

How can I achieve this??

Simply use an `order by ID, RecordType` to ensure the rows appear in the correct order — Nick.Mc, Mar 30 '17 at 22:37

Hadi · Accepted Answer · 2017-03-31T03:08:37.087

you can use an OLEDB Source with a similar SqlCommand to generate a column of type NVARCHAR that contains the whole row that you need to export to a flat file.

Note: Flat File must contains only one column in the flat file connection manager of type DT_WSTR and length equal to 4000 or higher if needed and map the column generated from the sqlCommand to it

SELECT  CAST(DataRow as NVARCHAR(4000))  as DataRow FROM (

SELECT ID ,'M' as RowType ,  
       'M   ' + CAST(ID as VARCHAR(255)) + '    ' + MField1  AS DataRow 
FROM Master

UNION ALL

SELECT IDMASTER ,'D' as RowType,  
       'D   ' + CAST(ID as VARCHAR(255)) + '    ' + CAST(IDMASTER as VARCHAR(255)) + '  ' + DetField1  AS DataRow  
FROM Det
        ) AS TBL
ORDER BY ID ASC, RowType DESC

I made a little experiment in sql server

CREATE TABLE #Master (ID INT, MField1 VARCHAR(255))
CREATE TABLE #Det (ID INT, IDMaster INT , DetField1 VARCHAR(255))

INSERT INTO #Master(ID, MField1)
VALUES (1,'MFieldVal1'),(2,'MFieldVal2')

INSERT INTO #Det(ID, IDMASTER,DetField1)
VALUES (1,1,'DFieldVal1'),(2,1,'DFieldVal2'),(3,2,'DFieldVal3')


SELECT  CAST(DataRow as NVARCHAR(4000))  as DataRow FROM (

SELECT ID ,'M' as RowType ,  'M ' + CAST(ID as VARCHAR(255)) + '    ' + MField1  AS DataRow FROM #Master

UNION ALL

SELECT IDMASTER ,'D' as RowType,  'D    ' + CAST(ID as VARCHAR(255)) + '    ' + CAST(IDMASTER as VARCHAR(255)) + '  ' + DetField1  AS DataRow  FROM #Det) AS TBL
ORDER BY ID ASC, RowType DESC

And the result was

Important Notes

In the SSIS the destination Flat File contains one column of type DT_WSTR but in real it contains Tab separated columns.
I used ine column, because it seems that you want to insert multiple structure into one flat file. So it is easier to do it this way

Thanks for your quick reply! This solution rely completely on the SqlCommand, I mean: all the formatting/conversion work needs to be done in the SQL command, I'm not an expert in SSIS, but I expected all the data manipulation work to be done in the SSIS data flow, but since I'm not an expert I may be wrong and the solution provided is a best practice. — Luigi Dallavalle, Mar 31 '17 at 07:31
This kind of data manipulation doesn't need the use of data flow task conversion components. And it is easy to achieve. — Hadi, Mar 31 '17 at 14:44
Give me a feedback when you try it. If it solved ur issue , it is good to [accept it](http://www.stackoverflow.com/tour) — Hadi, Mar 31 '17 at 14:45
I think you're right, and also formatting operations are better achieved in SQL. I ended up writing 2 views: one for the header data and one for the details data, every field is formatted as it's needed for the output file. In the dataflow I concatenate all the fields, then I "union all" them and sort them so that they're in the correct order. In the end I would had achieved the same result following exactly the solution you give above. I just thought that SSIS provided more functions to format\manipulate data. Thanks! — Luigi Dallavalle, Mar 31 '17 at 15:28

SSIS - Export Master-Detail data to a single flat file that contains both master and detail row

1 Answers1