CODEWITHSUNDEEP
ssis
Géza
Géza

Reputation: 2762

SSIS 2012: Get row of each group-by-aggregation

In a Data Flow I get the data with a Script Component that queries a webservice. The output is a table with payments from customers.

| PaymentId | CustomerId | Amount | PaymentDate |
| 2         | 1          | 10     | 01/01/2009  |
| 5         | 1          | 15     | 08/07/2013  |
| 1         | 2          | 20     | 05/08/2007  |
| 3         | 2          | 18     | 07/03/2011  |
| 6         | 2          | 32     | 02/09/2013  |
| 4         | 3          | 70     | 04/06/2012  |
| 7         | 3          | 61     | 02/03/2013  |

Among other things, I have to filter out following rows per customer:

And for each I need the PaymentId, CustomerId, Amount and PaymentDate for later processing.

I know how to use the Aggregate Transforamtion, group by CustomerId and use the appropriate Min/Max functions to get the requested VALUES.

But what is the best approach to get the complete ROWS?

Thanks in advance!

Upvotes: 0

Views: 908

Answers (1)

Mike Henderson
Mike Henderson

Reputation: 1317

If you're data source is a SQL database, do what you can with SQL, before you get to SSIS. This will generally give you the best performance.

Otherwise, suggest dumping data from the web service into a RAW file. Raw files are quite useful for staging data to do assorted transformations. more on RAW files

Upvotes: 1

Related Questions