(SSIS) Data Flow Task – export SQL to excel file

Question

I'm looking for a solution / tutorial following case. In the MS SQL database I have two tables (invoice, invoice item). I need to export data to excel.

My problem is the structure of the export excel file.

1. First record from table “invoice”
2. related records from table “invoice item”

3. empty row

4. Second record from table “invoice”

5. related records from table “invoice item”

6. empty row

7. Third record from table “invoice”

8. related records from table “invoice item”

9. empty row

10. ...

11. Last record from table “invoice”
12. related records from table “invoice item”
13. empty row

Does anyone know of a good example?

Answer 1

Answer for this question is little tricky.

• Create two variable called Invoice as object, invoiceId as string (I have supposed this is the linking field between these two table).
• Now using script task, list invoiceIds from invoice table and store it into Invoice object. In result set result name is 0 and VariableName is User::Invoice.
• Pull for each loop container and choose ADO enumerator then choose User::Invoice as ADO object. Now go to variable mapping and pass InvoiceID field into User::invloiceId variable.
• Now add data flow task. Choose three source. One from invloice table, another from InvoiceItem table and another as blank row(Convert using Data conversion if necessary since this is blank row)
• Now using parameter use invoiceID in where condition on each table.
• Use union all to do union from all three output.
• Now choose destination as excel and pass all data into it.

Hope this will solve your problem. Thanks.