2

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: enter image description here

What do I do here? there are no options.

Sample Data 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
acolene
  • 91
  • 2
  • 8
  • 3
    You can do joins and summaries inside dataflows in SSIS. Why don't you try a few things out. – Nick.Mc Feb 13 '20 at 23:01
  • 1
    Before doing a join you have to either sort each table, or tell SSIS that the data is sorted – jasonscript Feb 14 '20 at 05:52
  • 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 Feb 14 '20 at 17:47
  • @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 Feb 17 '20 at 13:27
  • 1
    Put DML in your question instead of an image for your source and I'll do it for you – KeithL Feb 17 '20 at 16:19
  • DML is SQL to create your source like select * from (values(a,b,c...),(ab,c...))a – KeithL Feb 17 '20 at 16:27
  • @KeithL I hope this is the SQL you were asking for!? I also tried to add the multicast to my Data Flow, but i am absolutely lost on how to use this. (See pic in Question) – acolene Feb 20 '20 at 14:11

1 Answers1

2

This is what your data flow will look like:

enter image description here

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

KeithL
  • 5,348
  • 3
  • 19
  • 25
  • Thank You. I will try this. I looked at multicast before but was not sure what to use to create the summary/header record. – acolene Feb 18 '20 at 13:03
  • 1
    BTW - Multicast object will make as many copies of the data as it is at that point as needed. – KeithL Feb 18 '20 at 15:27