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.