CODEWITHSUNDEEP
ssis
roxsap
roxsap

Reputation: 137

How to combine aggregated result and a table in SSIS?

I need to get the total transaction in the table and our formula involves a count distinct that's why I used derived column and aggregate transformation.

Now that I have the result, I want to add the output of aggregate transformation with another table. I tried union all, but it adds the output in the last entry.

Example: Table1

businessdate,storekey,itemkey,vf,trasnumber

1000,200,200,N,1234

1000,200,123,N,1235

1000,200,124,N,1235

1000,200,200,N,1236

1000,200,200,T,1236

AggregateTable

formula: (count distinct transnumber than have vf=n) subtract it with (count distinct transnumber that have vf=t)

result

4

I want the combined table to look like this:

Table2

businessdate,storekey,itemkey,vf,trasnumber,result

1000,200,200,N,1234,4

1000,200,123,N,1235,4

1000,200,124,N,1235,4

1000,200,200,N,1236,4

1000,200,200,T,1236,4

Would like to ask help on how I can add the result of aggregate transformation at the end of each row in table 1.

Thank you very much.

Upvotes: 1

Views: 334

Answers (2)

Mike Honey
Mike Honey

Reputation: 15017

I would add a "Dummy Merge Key" e.g. an integer with a fixed value of 1 to both data flow paths (from Table1 and from AggregateTable).

Then you can use a Merge Join transformation to connect the 2 paths (joined on the "Dummy Merge Key", and add the "result" column.

Upvotes: 1

MiguelH
MiguelH

Reputation: 1425

Add an OLE DB Command transformation after the aggregate. This would contain SQL similar to this update table1 set result = ? where businessdate = ? and storekey = ? You can then tie the "?" parameters to the output from the Aggregate transformation (I'm assuming here that businessdate and storekey are the keys that you've aggregated on)

Upvotes: 1

Related Questions