SQLTeam.com | Weblogs | Forums

Ssis aggregate sum


#1

Hi

I have data from one table going to another. there are 7 columns in both tables.i want to aggregate the balance field based on 2 fields but for all 7 columns to be filled with data in the second table as well. im using an aggregate box but when i tick the fields i want to aggerate on the dont come through to the second table.

how can i get around that


#2

Any fields you don't aggregate should become group by columns and available downstream. Can you possibly post screenshots so we can see what you see?


#3

if i group by to many fields its doesnt aggregate them correct.

i have say 4 columns

fundcode
accountcode
account name
balance

so i want to sum the balance on fundcode and account code but not account name as no account names is the same so then wont group correctly

ill add screen shot nexts once i get them


#4

as you can see in second pick if i dont tick it nothing in drop down box

anyway around this


#5

OK so say you have one fund code and one account code but two account names. If you sum the balance, grouping by fund code and account code, which account name gets the balance in the resulting row set, or do both account names get the balance?

FWIW, I often find complicated aggregation is easier to do in SQL (e.g. in your OLE DB Source ) than in the aggregation component, which is best suited to simple things.


#6

thats a good point actually they never said with names get it. i will have to ask them.

ya i was think an sql script might be the best way to go about this. just wanted to know if you could pull through the names even if you dont tick them in the aggregate box


#7

If you don't need that column, uncheck it at the source


#8

thanks for the help