 ᇽ ဒ ဒ ဘ စ တ လ ဉ ဴတ် ဘ ဠ ဒ ဴ ဒ ဒ တ းတးလရ ရ ငတ ဉ စ ဘ ္ ဃလ ရ ကလဝ ် ဠ ရ ပ် ပဘ ူတးရ နဏပရပန�ယ ဥဒ� here we will discuss about aggregate transformation. And what are the difference between SQL aggregate and SSIS aggregate? So let's start with aggregate transformation. So what is aggregate transformation? so it is used to perform aggregate operations on groups in a data set. Like we have a data set and it contains many columns and we want to do aggregate by any specific column then we can use aggregate transformation to do the same thing. The aggregate functions available are count, sum, average so these are the functions available in SQL Server as well as in SSIS. Aggregate transformation has one input and one or more than one output so let's move to our case study so this is our case study as we can see we have input table and it contains the information of phone number we have ID, phone type and phone number in ID, ID 1, 2, 3 in phone type business phone number, cell phone number, phone number so we have three type of phone numbers and we want to do the aggregation on ID and then phone type so first of all we want to do the aggregation on phone type second with the ID let's move to SQL Server management studio here we can see we have the same metadata with this phone aggregate table and we want to do the aggregate by using aggregate transformation so as we can see first of all we want to do aggregation with the phone type so we can execute our first query then we can see business phone has three count cell phone again has three count and home phone has four second we want to do the aggregation with ID then we can execute our second query here we can see ID 1 contain 2 ID 2 contain 3 and ID 3 contain 5 so we want to do this aggregation by SSIS so let's move to SSDT then we need to create one more practice right click on this SSIS practice create a new practice then we need to rename this practice with aggregate here we need to drag and drop our dataflow task so we can drag and drop then we need to double click on this dataflow task it will directly move to dataflow panel inside dataflow panel we need to configure our source transformation and destination so our source is OLEDB source so go to other sources then we can drag and drop our OLEDB source now we need to configure our OLEDB source so double click on this OLEDB source OLEDB source editor window will be open now we need to configure our connection manager so click on new here we have already made the connection so delete this connection and create a new connection click on new here we need to select our server name so this is our server name I am going to use window authentication so there is no need to use ID and password here we need to select our database name so database is SSIS test now we can check our connection connection succeed now click on OK so we have done with connection manager now we need to select the table so as we can see we have already typed the query so we can copy this query we need to change data access mode with SQL command here we need to paste the query now go to all columns we want all the columns so click on OK so we have done our source now we need to configure our transformation so our transformation is aggregate transformation inside this common we need to drag and drop aggregate transformation and connect with source now we need to double click on this aggregate here we have the columns so first of all we want to do the aggregation with phone type we need to select the phone type and select phone number so here we can see we have input columns output areas then operation so we want do the aggregation with phone type so our first operation is OK in our second operation we want count now click on OK so we have done our aggregation with phone type now we need to set up our destination so we don't want to load data any destination we want to just check our data so we need to drag and drop multicast go to on aggregate connect with multicast here we need to enable our data viewer so enable data viewer now I am going to execute these packages so as we can see phone type B, C and H 3, 3 and 4 now move to SQL Server Management Studio and execute our first query so once we execute our first query then we can see we are getting the same metadata as we are expected now we can stop our practice so here we can see it doesn't support any error output so here we can check we don't have any error output so aggregate transformation doesn't support error output next is so aggregate transformation has one output and in advance mode we can configure more than one output each will contain different aggregation so first we have done with phone type second we want to do the aggregation with id so double click on this aggregate so here we can see we have this advance so click on this advance as we can see we have only one aggregate here we can configure our second aggregate so we can click with aggregate 2 and this is for id so here we need to configure our second so this time we want to do the aggregate with id and we want to count with phone number so here we can change this phone number click on ok now we need to drag and drop one more multicast and here we can see we have one more output so we can select so this is for id and this is for phone type so here we need to enable data for id then we need to execute our package so as we can see this is for id and this is for phone type so we are getting the multiple aggregate at a single time so this is the difference between sequence over and ssis in ssis we can do multiple aggregate at a single time multiple aggregate at a single time we can also use aggregate to load unique data by applying group by to all columns so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos thank you so much