Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Aggregator transformation.....how to get all columns in output Expand / Collapse
Author
Message
Posted Thursday, May 29, 2014 12:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 8, 2014 8:16 PM
Points: 6, Visits: 25
Hi all,

I am trying to use Aggregator transformation to do the sum on one column based on group by on another column, but when i do it i am getting on these two columns in the output records... I have 23 other columns that i want as output column. How to achieve this in ssis.

Below is what i have in my package.

Source which has 25 columns
Aggregator ( group by on column1 and sum on column2)
output ( I need all 25 columns) ?
Post #1575761
Posted Friday, May 30, 2014 11:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 40,549, Visits: 37,791
sattar419 10789 (5/29/2014)
Hi all,

I am trying to use Aggregator transformation to do the sum on one column based on group by on another column, but when i do it i am getting on these two columns in the output records... I have 23 other columns that i want as output column. How to achieve this in ssis.

Below is what i have in my package.

Source which has 25 columns
Aggregator ( group by on column1 and sum on column2)
output ( I need all 25 columns) ?


I can't speak for SSIS but here's how I might do it in T-SQL... that is, if it made sense to do so. The "aggregator" will give you the sum of all Column2s grouped by Column1 and will be repeated for each unique value for Column1 (as in "group").

SELECT Column1
,Aggregator = SUM(Column2) OVER (PARTITION BY Column1)
,Column2
,Column3
,Column4
,Column5
,Column6
,Column7
,Column8
,Column9
,Column10
,Column11
,Column12
,Column13
,Column14
,Column15
,Column16
,Column17
,Column18
,Column19
,Column20
,Column21
,Column22
,Column23
,Column24
,Column25
FROM dbo.YourTable


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1576340
Posted Wednesday, June 4, 2014 12:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:51 AM
Points: 7,078, Visits: 17,185
Jeff's suggestion is a good one. Aggregation in SSIS is a blocking transformation and should be avoided if possible. It's also slower than the SQL Server database engine.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

Never attribute to malice that which is adequately explained by stupidity
Post #1577215
Posted Wednesday, June 4, 2014 12:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 23, 2016 6:13 AM
Points: 15,149, Visits: 13,035
Phil Parkin (6/4/2014)
Jeff's suggestion is a good one. Aggregation in SSIS is a blocking transformation and should be avoided if possible. It's also slower than the SQL Server database engine.


And you cannot do window aggregations with that component, which seems to be the use case here.
Conclusion: use TSQL




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577217
Posted Friday, July 25, 2014 6:01 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Saturday, March 19, 2016 1:53 AM
Points: 776, Visits: 1,556
In the Aggregator transform you select columns to group by and those to aggregate on, so the other 23 would indicate a group by. Note that you can use this Aggregator instead of t-sql if you're data is not all on the same machine. SSIS is meant for pulling data from different systems and will perform better in this regard. If all your data is on the same machine, use t-sql in the data source instead.
Best
Post #1596436
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse