{"id":1376,"date":"2017-01-12T13:01:17","date_gmt":"2017-01-12T07:31:17","guid":{"rendered":"http:\/\/stepbystepschools.net\/?p=1376"},"modified":"2017-01-12T13:01:17","modified_gmt":"2017-01-12T07:31:17","slug":"aggregation-transformation-component-in-ssis","status":"publish","type":"post","link":"https:\/\/www.stepbystepschools.net\/?p=1376","title":{"rendered":"Aggregation transformation component in SSIS"},"content":{"rendered":"<p>This is the scenario faced by a candidate when he had repeated values in his SQL Server records as shown in the image down below.<\/p>\n<p><a href=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/1-8.jpg\" rel=\"attachment wp-att-1377\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-1377\" src=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/1-8.jpg\" alt=\"1\" width=\"424\" height=\"345\" srcset=\"https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/1-8.jpg 424w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/1-8-300x244.jpg 300w\" sizes=\"(max-width: 424px) 100vw, 424px\" \/><\/a><\/p>\n<p>If we try to run the package it will throw us an error,<\/p>\n<h3>Error:<\/h3>\n<p><a href=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/2-8.jpg\" rel=\"attachment wp-att-1378\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-1378\" src=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/2-8.jpg\" alt=\"2\" width=\"545\" height=\"295\" srcset=\"https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/2-8.jpg 545w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/2-8-300x162.jpg 300w\" sizes=\"(max-width: 545px) 100vw, 545px\" \/><\/a><\/p>\n<p><a href=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/3-8.jpg\" rel=\"attachment wp-att-1379\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-1379\" src=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/3-8.jpg\" alt=\"3\" width=\"1133\" height=\"312\" srcset=\"https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/3-8.jpg 1133w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/3-8-300x83.jpg 300w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/3-8-768x211.jpg 768w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/3-8-1024x282.jpg 1024w\" sizes=\"(max-width: 1133px) 100vw, 1133px\" \/><\/a><\/p>\n<p>The error says that the pivot key has a duplicate value.<\/p>\n<h3>Solution:<\/h3>\n<p>To solve this error we will add a new component on our SSIS package available from SSIS toolbox under transformationknown as Aggregation Transformation. With the use of this component duplicate values will be removedand it will become one while Sales Amount gets aggregated.<\/p>\n<p><a href=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/4-7.jpg\" rel=\"attachment wp-att-1380\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-1380\" src=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/4-7.jpg\" alt=\"4\" width=\"628\" height=\"287\" srcset=\"https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/4-7.jpg 628w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/4-7-300x137.jpg 300w\" sizes=\"(max-width: 628px) 100vw, 628px\" \/><\/a><\/p>\n<p>This new Aggregate component is asked by candidate of <a href=\"http:\/\/www.learnmsbitutorials.net\/training.php\" target=\"_blank\">MSBI training<\/a> to place it between \u201cOLE DB Source\u201d and \u201cSort\u201d component.<\/p>\n<p><a href=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/5-6.jpg\" rel=\"attachment wp-att-1381\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-1381\" src=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/5-6.jpg\" alt=\"5\" width=\"628\" height=\"364\" srcset=\"https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/5-6.jpg 628w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/5-6-300x174.jpg 300w\" sizes=\"(max-width: 628px) 100vw, 628px\" \/><\/a><\/p>\n<p>To remove redcross mark seen over \u201cAggregate\u201d component even after connecting data path to each component. Double click and open its editor window to configure it.<\/p>\n<p><a href=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/6-4.jpg\" rel=\"attachment wp-att-1382\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-1382\" src=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/6-4.jpg\" alt=\"6\" width=\"414\" height=\"324\" srcset=\"https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/6-4.jpg 414w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/6-4-300x235.jpg 300w\" sizes=\"(max-width: 414px) 100vw, 414px\" \/><\/a><\/p>\n<p>After Editor window is open set \u201cSalesAmount\u201d operation field to \u201cSum\u201d as shown in the image down below.<\/p>\n<p><a href=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/7-4.jpg\" rel=\"attachment wp-att-1383\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-1383\" src=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/7-4.jpg\" alt=\"7\" width=\"627\" height=\"511\" srcset=\"https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/7-4.jpg 627w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/7-4-300x244.jpg 300w\" sizes=\"(max-width: 627px) 100vw, 627px\" \/><\/a><\/p>\n<p>After configuring the Aggregate transformation, we then execute the package,<\/p>\n<p><a href=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/8-2.jpg\" rel=\"attachment wp-att-1384\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-1384\" src=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/8-2.jpg\" alt=\"8\" width=\"542\" height=\"264\" srcset=\"https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/8-2.jpg 542w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/8-2-300x146.jpg 300w\" sizes=\"(max-width: 542px) 100vw, 542px\" \/><\/a><\/p>\n<p>The output shown below is from the laptop of <a href=\"http:\/\/www.learnmsbitutorials.net\/training.php\" target=\"_blank\">MSBI training<\/a> candidate where duplicate values for \u201cJan\u201d and \u201cSun\u201d has their \u201cSales Amount\u201d field aggregated.<\/p>\n<p><a href=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/9-1.jpg\" rel=\"attachment wp-att-1385\"><img loading=\"lazy\" class=\"alignnone size-full wp-image-1385\" src=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/9-1.jpg\" alt=\"9\" width=\"535\" height=\"186\" srcset=\"https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/9-1.jpg 535w, https:\/\/www.stepbystepschools.net\/wp-content\/uploads\/2017\/01\/9-1-300x104.jpg 300w\" sizes=\"(max-width: 535px) 100vw, 535px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is the scenario faced by a candidate when he had repeated values in his SQL Server records as shown in the image down below. If we try to run the package it will throw us an error, Error: The &hellip; <a href=\"https:\/\/www.stepbystepschools.net\/?p=1376\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":40,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[106],"tags":[157,155,151,108,119,159,158],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v18.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Aggregation transformation component in SSIS - Learn MVC, Angular, Architecture, MSBI, Data Science in Mumbai<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.stepbystepschools.net\/?p=1376\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Aggregation transformation component in SSIS - Learn MVC, Angular, Architecture, MSBI, Data Science in Mumbai\" \/>\n<meta property=\"og:description\" content=\"This is the scenario faced by a candidate when he had repeated values in his SQL Server records as shown in the image down below. If we try to run the package it will throw us an error, Error: The &hellip; Continue reading &rarr;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.stepbystepschools.net\/?p=1376\" \/>\n<meta property=\"og:site_name\" content=\"Learn MVC, Angular, Architecture, MSBI, Data Science in Mumbai\" \/>\n<meta property=\"article:published_time\" content=\"2017-01-12T07:31:17+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/1-8.jpg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"StepByStepSchoolsSupport\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.stepbystepschools.net\/#website\",\"url\":\"https:\/\/www.stepbystepschools.net\/\",\"name\":\"Learn MVC, Angular, Architecture, MSBI, Data Science in Mumbai\",\"description\":\"MVC 5\/Core, AngularJS 1.x, 2.0, 4.0, Design Pattern, Architecture, SSIS, SSAS, SSRS, Data Science, Big data, Hadoop classroom training\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.stepbystepschools.net\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.stepbystepschools.net\/?p=1376#primaryimage\",\"url\":\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/1-8.jpg\",\"contentUrl\":\"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/1-8.jpg\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.stepbystepschools.net\/?p=1376#webpage\",\"url\":\"https:\/\/www.stepbystepschools.net\/?p=1376\",\"name\":\"Aggregation transformation component in SSIS - Learn MVC, Angular, Architecture, MSBI, Data Science in Mumbai\",\"isPartOf\":{\"@id\":\"https:\/\/www.stepbystepschools.net\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.stepbystepschools.net\/?p=1376#primaryimage\"},\"datePublished\":\"2017-01-12T07:31:17+00:00\",\"dateModified\":\"2017-01-12T07:31:17+00:00\",\"author\":{\"@id\":\"https:\/\/www.stepbystepschools.net\/#\/schema\/person\/7131e4ee1c5673879e6029d14ec2f655\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.stepbystepschools.net\/?p=1376#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.stepbystepschools.net\/?p=1376\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.stepbystepschools.net\/?p=1376#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.stepbystepschools.net\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Aggregation transformation component in SSIS\"}]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.stepbystepschools.net\/#\/schema\/person\/7131e4ee1c5673879e6029d14ec2f655\",\"name\":\"StepByStepSchoolsSupport\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.stepbystepschools.net\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/cecaf2a0ee369391127b0159c266979d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/cecaf2a0ee369391127b0159c266979d?s=96&d=mm&r=g\",\"caption\":\"StepByStepSchoolsSupport\"},\"url\":\"https:\/\/www.stepbystepschools.net\/?author=40\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Aggregation transformation component in SSIS - Learn MVC, Angular, Architecture, MSBI, Data Science in Mumbai","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.stepbystepschools.net\/?p=1376","og_locale":"en_US","og_type":"article","og_title":"Aggregation transformation component in SSIS - Learn MVC, Angular, Architecture, MSBI, Data Science in Mumbai","og_description":"This is the scenario faced by a candidate when he had repeated values in his SQL Server records as shown in the image down below. If we try to run the package it will throw us an error, Error: The &hellip; Continue reading &rarr;","og_url":"https:\/\/www.stepbystepschools.net\/?p=1376","og_site_name":"Learn MVC, Angular, Architecture, MSBI, Data Science in Mumbai","article_published_time":"2017-01-12T07:31:17+00:00","og_image":[{"url":"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/1-8.jpg"}],"twitter_card":"summary_large_image","twitter_misc":{"Written by":"StepByStepSchoolsSupport","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebSite","@id":"https:\/\/www.stepbystepschools.net\/#website","url":"https:\/\/www.stepbystepschools.net\/","name":"Learn MVC, Angular, Architecture, MSBI, Data Science in Mumbai","description":"MVC 5\/Core, AngularJS 1.x, 2.0, 4.0, Design Pattern, Architecture, SSIS, SSAS, SSRS, Data Science, Big data, Hadoop classroom training","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.stepbystepschools.net\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.stepbystepschools.net\/?p=1376#primaryimage","url":"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/1-8.jpg","contentUrl":"http:\/\/stepbystepschools.net\/wp-content\/uploads\/2017\/01\/1-8.jpg"},{"@type":"WebPage","@id":"https:\/\/www.stepbystepschools.net\/?p=1376#webpage","url":"https:\/\/www.stepbystepschools.net\/?p=1376","name":"Aggregation transformation component in SSIS - Learn MVC, Angular, Architecture, MSBI, Data Science in Mumbai","isPartOf":{"@id":"https:\/\/www.stepbystepschools.net\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.stepbystepschools.net\/?p=1376#primaryimage"},"datePublished":"2017-01-12T07:31:17+00:00","dateModified":"2017-01-12T07:31:17+00:00","author":{"@id":"https:\/\/www.stepbystepschools.net\/#\/schema\/person\/7131e4ee1c5673879e6029d14ec2f655"},"breadcrumb":{"@id":"https:\/\/www.stepbystepschools.net\/?p=1376#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.stepbystepschools.net\/?p=1376"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.stepbystepschools.net\/?p=1376#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.stepbystepschools.net\/"},{"@type":"ListItem","position":2,"name":"Aggregation transformation component in SSIS"}]},{"@type":"Person","@id":"https:\/\/www.stepbystepschools.net\/#\/schema\/person\/7131e4ee1c5673879e6029d14ec2f655","name":"StepByStepSchoolsSupport","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.stepbystepschools.net\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/cecaf2a0ee369391127b0159c266979d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/cecaf2a0ee369391127b0159c266979d?s=96&d=mm&r=g","caption":"StepByStepSchoolsSupport"},"url":"https:\/\/www.stepbystepschools.net\/?author=40"}]}},"jetpack_featured_media_url":"","jetpack_publicize_connections":[],"_links":{"self":[{"href":"https:\/\/www.stepbystepschools.net\/index.php?rest_route=\/wp\/v2\/posts\/1376"}],"collection":[{"href":"https:\/\/www.stepbystepschools.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stepbystepschools.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stepbystepschools.net\/index.php?rest_route=\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stepbystepschools.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1376"}],"version-history":[{"count":1,"href":"https:\/\/www.stepbystepschools.net\/index.php?rest_route=\/wp\/v2\/posts\/1376\/revisions"}],"predecessor-version":[{"id":1386,"href":"https:\/\/www.stepbystepschools.net\/index.php?rest_route=\/wp\/v2\/posts\/1376\/revisions\/1386"}],"wp:attachment":[{"href":"https:\/\/www.stepbystepschools.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1376"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stepbystepschools.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1376"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stepbystepschools.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1376"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}