{"id":9767,"date":"2022-09-12T21:24:11","date_gmt":"2022-09-12T21:24:11","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=9767"},"modified":"2024-06-07T09:12:07","modified_gmt":"2024-06-07T09:12:07","slug":"compare-records-ssis-using-hash","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/","title":{"rendered":"How to compare records in SSIS using hash"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

In this blog,\u00a0\u00a0we are going to learn to how to compare records in SSIS using hash method. We will use\u00a0FREE<\/strong> Template Transform<\/a> component along with Sort Transform, Merge Join\u00a0Transform and Conditional Split\u00a0Transform. We will see how to produce hash value from multiple columns and later compare source\\target hash values to detect changes\\new rows.<\/p>\n

Prerequisites<\/span><\/h2>\n

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:<\/p>\n

    \n
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\n
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven’t already).<\/li>\n
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\n<\/ol>\n<\/div>\n

    Data Hashing<\/span><\/h2>\n

    Here is sample design how to compare records using hash method<\/p>\n

    <\/p>\n

      \n
    1. In Step 1, we need two data tables for record comparison from source and destination.Source Data:<\/strong>
      \n      \"\"<\/a>Target Data:<\/strong>
      \n      \"\"<\/a><\/li>\n
    2. In Step 2, both the source and target data table records would be hash using the ZS Template Transform component as below. You can use FUN_MD5<\/a> to produce hash from multiple columns and. Please make sure that you change the Output Column name as below e.g. HashCrm or HashSQL\"\"<\/a><\/li>\n
    3. In Step 3, both the source and target data table need to be sorted by primary key using SSIS “Sort Transformation Editor” component as shown in above sample design screenshot.<\/li>\n
    4. In Step 4,\u00a0both the source and target data table need to be merge using SSIS “Merge Join” component providing appropriate join type and key mapping
      \nas below      \"\"<\/a><\/li>\n
    5. In Step 4, we adding the conditional split with appropriate condition based on your need e.g. we want to check similar records as below\"\"<\/a><\/li>\n
    6. From conditional split, we can feed the modified or same records into destination component based on our needs.<\/li>\n
    7. This is how we can compare the data for source and target tables for updated records and also we can set up different Merge Join and conditional split for desired set of output records set.<\/li>\n<\/ol>\n

      Detect Update\/Insert<\/h2>\n
        \n
      1. In previous section, we have chosen the updated records using hash. Here are going to see the Updated\/New records using Left Outer Join as belowLeft Outer Join<\/strong>
        \n        \"\"<\/a><\/li>\n
      2. Here is conditional split criteria for Update\/New records as below\"\"<\/a><\/li>\n<\/ol>\n

        Conclusion<\/h2>\n

        In this tutorial we\u00a0learned how to compare the records in SSIS using hash method.\u00a0If you want take a look it then download the ZappySys SSIS PowerPack here<\/a>.<\/p>\n

         <\/p>\n","protected":false},"excerpt":{"rendered":"

        Introduction In this blog,\u00a0\u00a0we are going to learn to how to compare records in SSIS using hash method. We will use\u00a0FREE Template Transform component along with Sort Transform, Merge Join\u00a0Transform and Conditional Split\u00a0Transform. We will see how to produce hash value from multiple columns and later compare source\\target hash values to detect changes\\new rows. Data […]<\/p>\n","protected":false},"author":11,"featured_media":9773,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[600,598,599,267],"tags":[601,12],"yoast_head":"\r\nHow to compare records in SSIS using hash | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"How to compare records in SSIS using hash - ZappySys Blog\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to compare records in SSIS using hash | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"How to compare records in SSIS using hash - ZappySys Blog\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2022-09-12T21:24:11+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2024-06-07T09:12:07+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2022\/09\/hash-data-compare-ssis-1.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"342\" \/>\r\n\t<meta property=\"og:image:height\" content=\"285\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"Sudhir Dandale\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sudhir Dandale\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/\",\"url\":\"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/\",\"name\":\"How to compare records in SSIS using hash | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2022\/09\/hash-data-compare-ssis-1.png\",\"datePublished\":\"2022-09-12T21:24:11+00:00\",\"dateModified\":\"2024-06-07T09:12:07+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/b30eb07e5e6ce25a987c3fbe9b6b6a4e\"},\"description\":\"How to compare records in SSIS using hash - ZappySys Blog\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2022\/09\/hash-data-compare-ssis-1.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2022\/09\/hash-data-compare-ssis-1.png\",\"width\":342,\"height\":285},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to compare records in SSIS using hash\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/b30eb07e5e6ce25a987c3fbe9b6b6a4e\",\"name\":\"Sudhir Dandale\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/2e8d56e3752adc0d18866502c3e5777c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/2e8d56e3752adc0d18866502c3e5777c?s=96&d=mm&r=g\",\"caption\":\"Sudhir Dandale\"},\"url\":\"https:\/\/zappysys.com\/blog\/author\/sdandale\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to compare records in SSIS using hash | ZappySys Blog","description":"How to compare records in SSIS using hash - ZappySys Blog","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:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/","og_locale":"en_US","og_type":"article","og_title":"How to compare records in SSIS using hash | ZappySys Blog","og_description":"How to compare records in SSIS using hash - ZappySys Blog","og_url":"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/","og_site_name":"ZappySys Blog","article_published_time":"2022-09-12T21:24:11+00:00","article_modified_time":"2024-06-07T09:12:07+00:00","og_image":[{"width":342,"height":285,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2022\/09\/hash-data-compare-ssis-1.png","type":"image\/png"}],"author":"Sudhir Dandale","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Sudhir Dandale","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/","url":"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/","name":"How to compare records in SSIS using hash | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2022\/09\/hash-data-compare-ssis-1.png","datePublished":"2022-09-12T21:24:11+00:00","dateModified":"2024-06-07T09:12:07+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/b30eb07e5e6ce25a987c3fbe9b6b6a4e"},"description":"How to compare records in SSIS using hash - ZappySys Blog","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2022\/09\/hash-data-compare-ssis-1.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2022\/09\/hash-data-compare-ssis-1.png","width":342,"height":285},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/compare-records-ssis-using-hash\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to compare records in SSIS using hash"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/b30eb07e5e6ce25a987c3fbe9b6b6a4e","name":"Sudhir Dandale","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/2e8d56e3752adc0d18866502c3e5777c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/2e8d56e3752adc0d18866502c3e5777c?s=96&d=mm&r=g","caption":"Sudhir Dandale"},"url":"https:\/\/zappysys.com\/blog\/author\/sdandale\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/9767"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=9767"}],"version-history":[{"count":11,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/9767\/revisions"}],"predecessor-version":[{"id":11093,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/9767\/revisions\/11093"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/9773"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=9767"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=9767"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=9767"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}