{"id":926,"date":"2017-01-10T20:27:45","date_gmt":"2017-01-10T20:27:45","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=926"},"modified":"2025-10-30T23:27:53","modified_gmt":"2025-10-30T23:27:53","slug":"how-to-remove-invalid-characters-from-xml-using-ssis-and-regex","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/","title":{"rendered":"How to remove invalid characters from XML using SSIS and Regex"},"content":{"rendered":"

\u00a0Introduction<\/h2>\n

In this blog post, you will see how to remove invalid characters from XML using SSIS. We will use the search and replace feature of the\u00a0Advanced File System Task<\/a>.<\/p>\n

Remove Invalid characters from XML.<\/h2>\n

The XML file specification specifies which characters can be part of XML data and which should be avoided. If you use an invalid character, the XML Parser can throw an error indicating that it found an invalid character. Here is a very useful link<\/a> that explains which characters are allowed in XML and which are not.<\/p>\n

Using SSIS and Regex to clean invalid XML Characters<\/h2>\n

To clean invalid characters, we can use the following regular expression. This basically removes any characters outside the allowed character range.<\/p>\n

[^\\x09\\x0A\\x0D\\x20-\\xD7FF\\xE000-\\xFFFD\\x10000-x10FFFF]<\/pre>\n
The above expression is sufficient for most cases, but it is not the best solution. If you really want the most accurate expression\u00a0per the\u00a0XML Specs,<\/a> <\/span>use the expression below.<\/span>\u00a0It can be SLOW for large XML files<\/b>.<\/p>\n
[^\\u0009\\u000a\\u000d\\u0020-\\ud7ff\\ue000-\\ufffd]|([\\ud800-\\udbff](?![\\udc00-\\udfff]))|((?<![\\ud800-\\udbff])[\\udc00-\\udfff])<\/pre>\n
Remove any characters<\/h2>\n
If you don’t have a range of valid characters but you know a list of invalid characters, use the expression below (without the ^). For example, if you want to remove A, C, and E; then use the below<\/p>\n
[ACE]\r\n\r\n--OR-- Use hex codes\r\n\r\n[\\x41\\x43\\x45]<\/pre>\n
You can also use a range<\/p>\n[0-9A-Za-z]\n
Step-By-Step Example<\/h2>\n
    \n
  1. Download and install SSIS PowerPack from here.<\/a><\/li>\n
  2. Create a new SSIS Project.<\/li>\n
  3. Drag the ZS Advanced File System Task<\/a> from the SSIS Toolbox to the control flow designer.<\/li>\n
  4. Double-click the task to edit its properties, as shown below. Enter the following expression for a Regular Expression.\n
    [^\\x09\\x0A\\x0D\\x20-\\xD7FF\\xE000-\\xFFFD\\x10000-x10FFFF]<\/pre>\n<\/li>\n
  5. Save the package and execute. After you run the task, it will remove (replace with blank) any invalid character in your XML file.<\/li>\n<\/ol>\n
    Here is the screenshot of the Task configuration.<\/p>\n
    \"\"<\/a>
    SSIS Advanced File System Task – Search and Replace Option -Remove invalid XML characters using Regex (Regular Expression)<\/p><\/div>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    \u00a0Introduction In this blog post, you will see how to remove invalid characters from XML using SSIS. We will use the search and replace feature of the\u00a0Advanced File System Task. Remove Invalid characters from XML. The XML file specification specifies which characters can be part of XML data and which should be avoided. If you […]<\/p>\n","protected":false},"author":1,"featured_media":11598,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47],"tags":[161,12,79,4,7],"yoast_head":"\r\nHow to remove invalid characters from XML using SSIS and Regex | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to remove invalid characters from XML using SSIS Advanced File System Task. Use Regular expression for search and replace operation on XML file.\" \/>\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\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/\" \/>\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 remove invalid characters from XML using SSIS and Regex | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to remove invalid characters from XML using SSIS Advanced File System Task. Use Regular expression for search and replace operation on XML file.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2017-01-10T20:27:45+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-10-30T23:27:53+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/SSIS-Advanced-File-System-Task-Search-and-Replace-Option-Remove-invalid-XML-characters-using-Regex-Regular-Expression.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"820\" \/>\r\n\t<meta property=\"og:image:height\" content=\"595\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\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\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/\",\"url\":\"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/\",\"name\":\"How to remove invalid characters from XML using SSIS and Regex | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/SSIS-Advanced-File-System-Task-Search-and-Replace-Option-Remove-invalid-XML-characters-using-Regex-Regular-Expression.png\",\"datePublished\":\"2017-01-10T20:27:45+00:00\",\"dateModified\":\"2025-10-30T23:27:53+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to remove invalid characters from XML using SSIS Advanced File System Task. Use Regular expression for search and replace operation on XML file.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/SSIS-Advanced-File-System-Task-Search-and-Replace-Option-Remove-invalid-XML-characters-using-Regex-Regular-Expression.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/SSIS-Advanced-File-System-Task-Search-and-Replace-Option-Remove-invalid-XML-characters-using-Regex-Regular-Expression.png\",\"width\":820,\"height\":595,\"caption\":\"SSIS Advanced File System Task - Search and Replace Option -Remove invalid XML characters using Regex (Regular Expression)\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to remove invalid characters from XML using SSIS and Regex\"}]},{\"@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\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to remove invalid characters from XML using SSIS and Regex | ZappySys Blog","description":"Learn how to remove invalid characters from XML using SSIS Advanced File System Task. Use Regular expression for search and replace operation on XML file.","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\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/","og_locale":"en_US","og_type":"article","og_title":"How to remove invalid characters from XML using SSIS and Regex | ZappySys Blog","og_description":"Learn how to remove invalid characters from XML using SSIS Advanced File System Task. Use Regular expression for search and replace operation on XML file.","og_url":"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2017-01-10T20:27:45+00:00","article_modified_time":"2025-10-30T23:27:53+00:00","og_image":[{"width":820,"height":595,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/SSIS-Advanced-File-System-Task-Search-and-Replace-Option-Remove-invalid-XML-characters-using-Regex-Regular-Expression.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/","url":"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/","name":"How to remove invalid characters from XML using SSIS and Regex | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/SSIS-Advanced-File-System-Task-Search-and-Replace-Option-Remove-invalid-XML-characters-using-Regex-Regular-Expression.png","datePublished":"2017-01-10T20:27:45+00:00","dateModified":"2025-10-30T23:27:53+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to remove invalid characters from XML using SSIS Advanced File System Task. Use Regular expression for search and replace operation on XML file.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/SSIS-Advanced-File-System-Task-Search-and-Replace-Option-Remove-invalid-XML-characters-using-Regex-Regular-Expression.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/SSIS-Advanced-File-System-Task-Search-and-Replace-Option-Remove-invalid-XML-characters-using-Regex-Regular-Expression.png","width":820,"height":595,"caption":"SSIS Advanced File System Task - Search and Replace Option -Remove invalid XML characters using Regex (Regular Expression)"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/how-to-remove-invalid-characters-from-xml-using-ssis-and-regex\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to remove invalid characters from XML using SSIS and Regex"}]},{"@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\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/926"}],"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\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=926"}],"version-history":[{"count":8,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/926\/revisions"}],"predecessor-version":[{"id":11599,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/926\/revisions\/11599"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/11598"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=926"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}