{"id":25967,"date":"2023-07-19T07:20:00","date_gmt":"2023-07-19T05:20:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=25967"},"modified":"2023-09-26T18:35:16","modified_gmt":"2023-09-26T16:35:16","slug":"calculate-distinct-rows-with-ssis","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/","title":{"rendered":"How to count distinct rows with SSIS?"},"content":{"rendered":"\n

How to calculate the number of distinct rows from a table or a file within a data flow using SSIS?<\/em><\/strong><\/h4>\n\n\n\n

Tutorial to learn how to count distinct rows in a SQL Server table or data files with SSIS and store it in a text file. To achieve this, we use the SSIS Aggregate transformation. This operation is the SSIS equivalent of executing a COUNT DISTINCT command in SQL Server or in standard SQL. Indeed, the COUNT DISTINCT aggregation option is used to calculate the unique rows in a table or file, within a data flow.<\/p>\n\n\n\n

1. Create an SSIS package to count distinct rows<\/h2>\n\n\n\n

One prerequisite is to have the Adventureworks<\/a> sample database installed and configured in one database. To begin, in the Data Flow, place a source, the SSIS Aggregate component, and a destination. For this example, the source will be the SSIS OLE DB Source component and the Flat File Destination will serve as the destination for exporting the result to a text file. In order to calculate the number of unique rows with SSIS, add these 3 components to the data flow: <\/p>\n\n\n\n

    \n
  1. The OLE DB Source<\/li>\n\n\n\n
  2. The SSIS Aggregate transformation<\/li>\n\n\n\n
  3. And the Flat File Destination<\/li>\n<\/ol>\n\n\n
    \n
    \"3
    3 SSIS components needed to calculate the number of distinct lines with SSIS<\/em><\/figcaption><\/figure><\/div>\n\n\n

    2. Point the OLE DB source component towards the SQL table<\/h2>\n\n\n\n

    Next, it is necessary to configure the SSIS Data Flow. Visual Studio will now display error messages on the Integration Services components. Afterward, double-click on the OLE DB Source component and select a table. For this example, we are selecting the HumanResources.Employee<\/em> table from the AdventureWorks database.<\/p>\n\n\n

    \n
    \"Configure
    Configure the connection to the source table to count separate rows<\/em><\/figcaption><\/figure><\/div>\n\n\n

    3. Configure the SSIS Aggregate Component with Count Distinct<\/h2>\n\n\n\n

    Once the source has been selected and configured, it is time to configure the SSIS Aggregate component. In the Aggregations tab, select the columns where the COUNT DISTINCT should be performed, that is, the columns to aggregate<\/a>. In the Operation column, choose COUNT DISTINCT.<\/p>\n\n\n

    \n
    \"Configure
    Configure the SSIS aggregation with Count Distinct<\/em><\/figcaption><\/figure><\/div>\n\n\n

    4. Link the Aggregate transformation to the target file<\/h2>\n\n\n\n

    Now, connect the SSIS Aggregate component to the final component, known as the Flat File Destination. Double-click on this component to select the destination file. Finally, in the Mapping tab, ensure that the columns are correctly selected.<\/p>\n\n\n

    \n
    \"Configure
    Configure the SSIS output mapping to using the editor<\/em><\/figcaption><\/figure><\/div>\n\n\n

    After configuring all the SSIS package components, there will no longer be any errors in the Data Flow.<\/p>\n\n\n

    \n
    \"SSIS
    SSIS Data Flow with the three components linked together<\/em><\/figcaption><\/figure><\/div>\n\n\n

    5. Execute the package to export the distinct row count<\/h2>\n\n\n\n

    Next, execute the SSIS package. The exported flat file will contain the distinct row count for the selected columns. The target file will now consist of a single row and the number 290, which corresponds to the number of rows in the input table.<\/p>\n\n\n

    \n
    \"Successful
    Successful execution of the SSIS Data Flow<\/em><\/figcaption><\/figure><\/div>\n\n\n

    Conclusion on the SSIS Aggregation Component<\/h3>\n\n\n\n

    This article demonstrates step by step how to perform a count distinct with SSIS, without writing any line of SQL code. For performance optimization, it is recommended to execute SQL operations directly on the SQL Server engine, thus reducing the data transfer between the SQL Server database<\/a> and the SSIS server. Integration Services packages enable all operations available in T-SQL through a graphical interface and a simplified industrialisation process. For example, here is how to pivot the rows of a SQL table into columns with SSIS<\/strong><\/a>.<\/p>\n\n\n\n

    \nhttps:\/\/expert-only.com\/en\/ssis\/pivot-rows-to-columns-with-ssis\/\n<\/div><\/figure>\n\n\n\n

    Of course, it is also possible to perform the same pivot operation, which is also very handy but a bit more complex in terms of coding, using the SQL Server PIVOT query<\/strong><\/a> directly from SSMS and only with T-SQL code.<\/p>\n\n\n\n

    \nhttps:\/\/expert-only.com\/en\/t-sql\/sql-server-pivot-query-example\/\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"

    How to calculate the number of distinct rows from a table or a file within a data flow using SSIS? Tutorial to learn how to count distinct rows in a SQL Server table or data files with SSIS and …<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":10869,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[516],"tags":[],"class_list":{"0":"post-25967","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-ssis"},"yoast_head":"\nHow to count distinct rows with SSIS? Integration Services<\/title>\n<meta name=\"description\" content=\"Count the distinct rows of a table or a data file using the SSIS aggregation component, equivalent to COUNT DISTINCT in Transact-SQL.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to count distinct rows with SSIS?\" \/>\n<meta property=\"og:description\" content=\"Count the distinct rows of a table or a data file using the SSIS aggregation component, equivalent to COUNT DISTINCT in Transact-SQL.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-19T05:20:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-09-26T16:35:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/texture-design-9D617D2FB8C_1920x1080.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"How to count distinct rows with SSIS?\",\"datePublished\":\"2023-07-19T05:20:00+00:00\",\"dateModified\":\"2023-09-26T16:35:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/\"},\"wordCount\":627,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/texture-design-9D617D2FB8C_1920x1080.jpg\",\"articleSection\":[\"SSIS\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/\",\"url\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/\",\"name\":\"How to count distinct rows with SSIS? Integration Services\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/texture-design-9D617D2FB8C_1920x1080.jpg\",\"datePublished\":\"2023-07-19T05:20:00+00:00\",\"dateModified\":\"2023-09-26T16:35:16+00:00\",\"description\":\"Count the distinct rows of a table or a data file using the SSIS aggregation component, equivalent to COUNT DISTINCT in Transact-SQL.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/texture-design-9D617D2FB8C_1920x1080.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/texture-design-9D617D2FB8C_1920x1080.jpg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to count distinct rows with SSIS?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to count distinct rows with SSIS? Integration Services","description":"Count the distinct rows of a table or a data file using the SSIS aggregation component, equivalent to COUNT DISTINCT in Transact-SQL.","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:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/","og_locale":"en_US","og_type":"article","og_title":"How to count distinct rows with SSIS?","og_description":"Count the distinct rows of a table or a data file using the SSIS aggregation component, equivalent to COUNT DISTINCT in Transact-SQL.","og_url":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2023-07-19T05:20:00+00:00","article_modified_time":"2023-09-26T16:35:16+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/texture-design-9D617D2FB8C_1920x1080.jpg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"How to count distinct rows with SSIS?","datePublished":"2023-07-19T05:20:00+00:00","dateModified":"2023-09-26T16:35:16+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/"},"wordCount":627,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/texture-design-9D617D2FB8C_1920x1080.jpg","articleSection":["SSIS"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/","url":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/","name":"How to count distinct rows with SSIS? Integration Services","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/texture-design-9D617D2FB8C_1920x1080.jpg","datePublished":"2023-07-19T05:20:00+00:00","dateModified":"2023-09-26T16:35:16+00:00","description":"Count the distinct rows of a table or a data file using the SSIS aggregation component, equivalent to COUNT DISTINCT in Transact-SQL.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/texture-design-9D617D2FB8C_1920x1080.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/texture-design-9D617D2FB8C_1920x1080.jpg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/ssis\/calculate-distinct-rows-with-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"How to count distinct rows with SSIS?"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/25967","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=25967"}],"version-history":[{"count":8,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/25967\/revisions"}],"predecessor-version":[{"id":27134,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/25967\/revisions\/27134"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/10869"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=25967"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=25967"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=25967"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}