How to create a CSV file in SSIS( 2016 and 2019) which will have 2 Table resultset ?

Question

How to create a CSV file in SSIS( 2016 and SSIS 2019) which will have 2 Table result set in a single CSV file ?

Table 1 is Header record

Table 2 is Detail record

Attached below is the expected Output.

The CSV file will be used for import process in a system. The file has 2 datasets coming from 2 tables and are required to be clubbed as shown in the expected o/p screenshot.

Answer 1

Hi @Anonymous ,

1.Please drag a Data Flow Task and load data from source table1 to Recordset Destination which can store data in object variable;
2.Then use Foreach Loop Container to read data from object variable and load data into a string variable;
3.Drag a Data Flow Task in the Foreach Loop Container and load data from source table2 to newtable2 in Flat File Destination;
4.Set the expression for [Flat File Destination].[Header] in properties of Data Flow Task2.

Please refer to the following pictures:









Best Regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I have tried the following steps:

1. Loaded first table in the same file using flat file connection manager 1
2. Loaded second table using the same file using flat file connection manager 2 .

It has worked in clubbing data in a single csv as header and detail.

Is the above steps still recommended in SSIS 2016 and SSIS 2019?

Answer 3

Wow man , that's new way of doing it . Appreciate your help