CODEWITHSUNDEEP
sqlsql-serverexcelssis
Bee
Bee

Reputation: 33

SSIS Aggregating specific columns where = XXXX from Excel Source

In SSIS, I'm taking data from an Excel Source. This is what my data looks like:

+----+---------+--------+
| id |   Pet   | Amount |
+----+---------+--------+
| 1  | Cat     | 3      |
| 2  | Cat     | 2      |
| 3  | Dog     | 2      |
| 4  | Hamster | 3      |
| 5  | Fish    | 7      |
| 6  | Cat     | 1      |
| 7  | Cat     | 1      |
+----+---------+--------+

Now, my destination is a SQL Server Destination.

I want to be able to only sum up the Amount columns that have Cat listed:

+----+---------+--------+
| id |   Pet   | Amount |
+----+---------+--------+
| >1 | Cat     | 3      |
| >2 | Cat     | 2      |
| 3  | Dog     | 2      |
| 4  | Hamster | 3      |
| 5  | Fish    | 7      |
| >6 | Cat     | 1      |
| >7 | Cat     | 1      |
+----+---------+--------+

The total would be 7. Is there any way I could do this with an Excel data source? I know if I had a SQL / OLE DB source, I could do a query such as

  SELECT columnname FROM table
       WHERE Pet="Cat"

Please correct me if I'm wrong and tell me if you need clarification.

Upvotes: 0

Views: 407

Answers (2)

Jayvee
Jayvee

Reputation: 10875

You can do it in SSIS by using a Conditional Split transformation after the excel source and select only the rows where Pet=="Cat". After the Conditional Split you should plug an Aggregate transformation to sum up "Amount". Alternatively you can omit the Conditional Split and get the sums directly from the Aggregate transformation.

Upvotes: 1

user3223048
user3223048

Reputation: 163

 SELECT sum (Amount) AS "Number of cats"
 FROM table
 where Pet="Cat"

Is that what you were looking for?

the output would be like this:

  ________________
  |Number of Cats |
  |______7________|

Upvotes: 0

Related Questions