{"id":8111,"date":"2019-10-08T23:12:08","date_gmt":"2019-10-08T23:12:08","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=8111"},"modified":"2019-11-18T14:55:05","modified_gmt":"2019-11-18T14:55:05","slug":"detect-missing-columns-in-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/","title":{"rendered":"How to detect missing columns in SSIS"},"content":{"rendered":"

How to detect extra columns or missing columns<\/h2>\n

\"ssisSometimes, we need to validate the input columns to check if there are some\u00a0extra columns or missing columns in SSIS. ZappySys includes some pretty nice tools, but\u00a0some of them cannot check the required values. To fix that problem, you can\u00a0complement these tools with our Regular Expression Parser Task.<\/p>\n

We will use Regular Expressions to validate the columns. For example, we want\u00a0to check that the file has 3 columns with specific names, if it contains extra\u00a0columns or there are missing some columns, we want to send an error. We will\u00a0show now how to do this.<\/p>\n

We will be using this\u00a0ZappySys SSIS PowerPack<\/a>\u00a0component to make things work:
\n

\n\n\n\n

\n\"SSIS<\/a><\/td>\n		SSIS Regex Task
\n<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  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>\r\n<\/ol><\/div>\n

    Creating a CSV file<\/h2>\n
      \n
    1. In order to start, we will create a CSV file with some columns. Create a\u00a0file named document.csv or any name of your preference.<\/li>\n
    2. Add data similar to the following:\n
      name,lastname,email\r\nClint,Eastwood,ceastwood@gmail.com\r\nCameron,Diaz,cdiaz@gmail.com<\/pre>\n
       <\/li>\n<\/ol>\n
      Create a connection in SSIS<\/h2>\n
        \n
      1. First of all, SSDT, open an SSIS project.<\/li>\n
      2. Secondly, in the connection manager, right-click and select a new file\u00a0connection.\n
      3. In the Connection, select the CSV file created before:\n
        \"Select<\/a>
        ssis select file connection<\/p><\/div><\/li>\n<\/ol>\n
         <\/p>\n
        ZS Regular Expression Parser to detect missing columns in SSIS<\/h2>\n
          \n
        1. Now, it is time to use the ZS Regular Expression Parser.<\/li>\n
        2. Secondly, drag and drop the ZS Regular Expression in the design pane:\n
          \"Regex
          SSIS Regular Expression parser<\/p><\/div><\/li>\n
        3. Also, open the task and select the connection in the ZS Regular\u00a0Expression task.<\/li>\n
        4. In addition, add the following expression in the Task:\n
          <\/div>\n
          ^name,lastname\\r\\n\r\n<\/pre>\n<\/li>\n
        5. In the previous scenario, the expression will fail because it is missing\u00a0the email.<\/li>\n
        6. Finally, the following Expression will pass, because the email is\u00a0included.\n
          \"Verify<\/a>
          SSIS verify missing columns in SSIS<\/p><\/div><\/li>\n<\/ol>\n
          Conclusion<\/h2>\n
          In this article, we learned how to validate and check the columns in a file in ZappySys\u00a0using the ZS Regular Expression Parser. In the example, we created an expression\u00a0to detect an exact match of columns. It will raise an error in case of a\u00a0failure.<\/p>\n
          If you want to enjoy this and other ZappySys tools, you can download our\u00a0product here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
          How to detect extra columns or missing columns Sometimes, we need to validate the input columns to check if there are some\u00a0extra columns or missing columns in SSIS. ZappySys includes some pretty nice tools, but\u00a0some of them cannot check the required values. To fix that problem, you can\u00a0complement these tools with our Regular Expression Parser […]<\/p>\n","protected":false},"author":5,"featured_media":3074,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[561,560,12],"yoast_head":"\r\nHow to detect missing columns in SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"In this article, we will show how to detect missing columns in SSIS. We will use regular expressions to validate missing or extra columns.\" \/>\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\/detect-missing-columns-in-ssis\/\" \/>\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 detect missing columns in SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"In this article, we will show how to detect missing columns in SSIS. We will use regular expressions to validate missing or extra columns.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2019-10-08T23:12:08+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2019-11-18T14:55:05+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/SSIS-Json-Source-Adapter.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"100\" \/>\r\n\t<meta property=\"og:image:height\" content=\"100\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys Team\" \/>\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=\"ZappySys Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/\",\"url\":\"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/\",\"name\":\"How to detect missing columns in SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/SSIS-Json-Source-Adapter.png\",\"datePublished\":\"2019-10-08T23:12:08+00:00\",\"dateModified\":\"2019-11-18T14:55:05+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e\"},\"description\":\"In this article, we will show how to detect missing columns in SSIS. We will use regular expressions to validate missing or extra columns.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/SSIS-Json-Source-Adapter.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/SSIS-Json-Source-Adapter.png\",\"width\":100,\"height\":100},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to detect missing columns in SSIS\"}]},{\"@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\/91b041e2dcf7ece5f068893c1a68ac6e\",\"name\":\"ZappySys Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g\",\"caption\":\"ZappySys Team\"},\"sameAs\":[\"https:\/\/zappysys.com\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/dcalbimonte\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to detect missing columns in SSIS | ZappySys Blog","description":"In this article, we will show how to detect missing columns in SSIS. We will use regular expressions to validate missing or extra columns.","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\/detect-missing-columns-in-ssis\/","og_locale":"en_US","og_type":"article","og_title":"How to detect missing columns in SSIS | ZappySys Blog","og_description":"In this article, we will show how to detect missing columns in SSIS. We will use regular expressions to validate missing or extra columns.","og_url":"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/","og_site_name":"ZappySys Blog","article_published_time":"2019-10-08T23:12:08+00:00","article_modified_time":"2019-11-18T14:55:05+00:00","og_image":[{"width":100,"height":100,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/SSIS-Json-Source-Adapter.png","type":"image\/png"}],"author":"ZappySys Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ZappySys Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/","url":"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/","name":"How to detect missing columns in SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/SSIS-Json-Source-Adapter.png","datePublished":"2019-10-08T23:12:08+00:00","dateModified":"2019-11-18T14:55:05+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e"},"description":"In this article, we will show how to detect missing columns in SSIS. We will use regular expressions to validate missing or extra columns.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/SSIS-Json-Source-Adapter.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/SSIS-Json-Source-Adapter.png","width":100,"height":100},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/detect-missing-columns-in-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to detect missing columns in SSIS"}]},{"@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\/91b041e2dcf7ece5f068893c1a68ac6e","name":"ZappySys Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g","caption":"ZappySys Team"},"sameAs":["https:\/\/zappysys.com"],"url":"https:\/\/zappysys.com\/blog\/author\/dcalbimonte\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8111"}],"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\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=8111"}],"version-history":[{"count":18,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8111\/revisions"}],"predecessor-version":[{"id":8133,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/8111\/revisions\/8133"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/3074"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=8111"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=8111"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=8111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}