How to merge source rows using SSIS Kingswaysoft without using a temp table

Question

I have a source table with multiple rows of data but with no header row. I need to migrate this data to D365 CRM as two entities; A header entity and the corresponding content/rows entity.

Is there any way to create the header entity in Kingswaysoft / SSIS where the header would represent all the rows with the same batch number and their total payment?

Preferably without a temp table? (If its not possible without a temp table).

Thanks in advance!!!

This is what i get when i add the multicast:

What do I do here? there are no options.

This is the SQL I wrote to merge the rows.

SELECT [business]
  ,[payment]
  ,[batch],
   SUM(CASE WHEN payment >= 0  THEN payment ELSE 0 END) pay,
   SUM(CASE WHEN payment >= 420  THEN 21.72 ELSE 0 END) 
   TransactionAmount1,
   SUM(CASE WHEN payment <= 299.99 AND payment >= 260 THEN 15.35 ELSE 0 
   END) TransactionAmount2,

   FROM [Payments]
   Group By batch, business_num

You can do joins and summaries inside dataflows in SSIS. Why don't you try a few things out. — Nick.McDermaid
Before doing a join you have to either sort each table, or tell SSIS that the data is sorted — jasonscript
You actually don't need a join. you need to load your source, then multicast. in one direction is your details and in another you run into an aggregation. Batch is the key between tables — KeithL
@KeithL I did look at multicast, but I wasn't sure how to use it. Since I have to convert the rows in groups (aggregation) into the header, I was not sure how to do this with the multicast! — acolene
Put DML in your question instead of an image for your source and I'll do it for you — KeithL

KeithL KeithL · Accepted Answer · 2020-02-17T16:23:03

This is what your data flow will look like:

In the aggregate group by batch,business and sum(payments). There is no need to create a headerID. Batch is your link.

How to merge source rows using SSIS Kingswaysoft without using a temp table

1 Answers