Search code examples
sql-serverssisflat-file

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

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??

Solution

  • 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

    enter image description here

    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