Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:46 PM
Points: 35,218, Visits: 31,676
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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 @ 11:58 PM
Points: 5,078, Visits: 11,862
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.
Post #1577215
Posted Wednesday, June 4, 2014 12:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:46 AM
Points: 12,959, Visits: 10,730
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 LessThanDot.

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


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:01 PM
Points: 356, Visits: 871
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