{"id":1590,"date":"2017-07-21T14:50:47","date_gmt":"2017-07-21T14:50:47","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1590"},"modified":"2019-02-15T14:23:24","modified_gmt":"2019-02-15T14:23:24","slug":"advanced-path-filtering-for-amazon-azure-storage-ssis-task","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/","title":{"rendered":"Filter by file size, name, date for Amazon \/ Azure Storage SSIS Task"},"content":{"rendered":"

\"\"<\/a><\/p>\n

Introduction<\/h2>\n

Sometimes you may need to make an advanced filtering on file path, file date, size etc in SSIS Amazon S3 Task<\/a> or\u00a0SSIS Azure Blob Storage Task<\/a> . For example, you have a directory “Customers<\/em>“, which contains client directories. Each of them contains a directory named “Invoices<\/em>“, which is partitioned by year. E.g. “Customers\/Microsoft\/Invoices\/2015”. So if you want to get all<\/em> invoices of all<\/em> customers of all<\/em> years, use Advanced Filter & Sorting\u00a0<\/em>option and configure Where<\/em> clause to use wildcards (*) like this:<\/p>\n

\"SSIS<\/a>

SSIS Amazon\/Azure Storage Task \u2013 To get all invoices of all customers of all years, use this Where clause:\u00a0FullPath LIKE ‘Customers\/*\/Invoices\/*’<\/em><\/p><\/div>\n

 <\/p>\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

     <\/p>\n

    Using Advanced Search \u2013 Regex \/ SQL Expression (Filter by file date, size, name)<\/span><\/h2>\n

    Sometimes it\u2019s necessary to filter file based on advanced attributes such as file Age (How old), File Size, Multiple File Patterns (e.g. Delete *.zip, *.gz, *.rar in one action)<\/p>\n

    You can check this link<\/a> or this link<\/a> for more information. For Azure Blob Storage Task<\/a> its almost same UI.<\/p>\n

    You can use Advanced Tab to filter further. First supply primary filter on Source Tab (e.g. mybucket\/*.* ) and then supply your advanced filter (e.g. you can exclude *.exe and *.msi file from your delete list as below screenshot, also only delete files between 1000 and 50000 bytes in file size).<\/p>\n

     <\/p>\n

    \"Delete<\/a>

    Select \/ Delete Amazon S3 File (Multiple) using Wildcard pattern<\/p><\/div>\n

     <\/p>\n

    \n
    \"Advanced

    Advanced Search in SSIS Amazon S3 Task \u2013 Use Regular Expression Patterns, SQL like expression for Sort \/ Filter<\/p><\/div>\n

    \n<\/div>\n

    \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nExamples:\r\n\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nAgeInDays < 5\u00a0\u00a0\/\/Get files created in last 5 days\r\nLastEditInDays > 3 \/\/Get files modified before 3 days\r\nAgeInDays > 10 AND LastEditInDays < 3\u00a0\u00a0\/\/Get files created before 10 days and modified in last 3 days\r\nSize < 1024\u00a0\u00a0\/\/Get files where File size is less than 1024 bytes\r\nCreationDate BETWEEN #2015-01-01# AND #2015-01-01#\u00a0\u00a0\r\nName IN ('File1.txt', 'Download.msi')\r\nFullPath LIKE 'mybucket\\somefolder\\month_*')\r\nExtension IN ('.txt','.csv')\r\nLastModifiedDate > #2013-01-01T16:00:00#\r\nIsReadOnly = True\r\n\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\n\/\/Supported Columns for expression:\r\n\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nName, FullPath, Extension, FolderPath, AgeInDays, LastEditInDays, Size, CreationDate, CreationDateUtc, LastModifiedDate, LastModifiedDateUtc, IsReadOnly<\/pre>\n
     <\/p>\n
    Select Amazon S3 files older than X days<\/span><\/h3>\n
    If you like to select \/ delete files older than certain days then use below technique.<\/p>\n
    AgeInDays > 10<\/pre>\n
      \n
    1. First Specify primary search criteria on Source tab<\/li>\n
    2. Go to Advanced Filter Tab and specify Following Pattern in\u00a0Where<\/strong>\u00a0Textbox (For example delete Files older than 10 days )\n
      \n
      <\/div>\n<\/div>\n<\/li>\n<\/ol>\n
      <\/h2>\n","protected":false},"excerpt":{"rendered":"
      Introduction Sometimes you may need to make an advanced filtering on file path, file date, size etc in SSIS Amazon S3 Task or\u00a0SSIS Azure Blob Storage Task . For example, you have a directory “Customers“, which contains client directories. Each of them contains a directory named “Invoices“, which is partitioned by year. E.g. “Customers\/Microsoft\/Invoices\/2015”. So […]<\/p>\n","protected":false},"author":1,"featured_media":1897,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[181,182,11],"tags":[218,217,187,188,219],"yoast_head":"\r\nFilter by file size, name, date for Amazon \/ Azure Storage SSIS Task | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Perform advanced file operations on Amazon Storage or Azure Storage by using Amazon\/Azure Storage Task for SSIS. Filter files using the wildcard (*) symbol.\" \/>\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\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Filter by file size, name, date for Amazon \/ Azure Storage SSIS Task | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Perform advanced file operations on Amazon Storage or Azure Storage by using Amazon\/Azure Storage Task for SSIS. Filter files using the wildcard (*) symbol.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/\" \/>\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-07-21T14:50:47+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2019-02-15T14:23:24+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-tip.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"180\" \/>\r\n\t<meta property=\"og:image:height\" content=\"180\" \/>\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\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/\",\"url\":\"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/\",\"name\":\"Filter by file size, name, date for Amazon \/ Azure Storage SSIS Task | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-tip.png\",\"datePublished\":\"2017-07-21T14:50:47+00:00\",\"dateModified\":\"2019-02-15T14:23:24+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Perform advanced file operations on Amazon Storage or Azure Storage by using Amazon\/Azure Storage Task for SSIS. Filter files using the wildcard (*) symbol.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-tip.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-tip.png\",\"width\":180,\"height\":180},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Filter by file size, name, date for Amazon \/ Azure Storage SSIS Task\"}]},{\"@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":"Filter by file size, name, date for Amazon \/ Azure Storage SSIS Task | ZappySys Blog","description":"Perform advanced file operations on Amazon Storage or Azure Storage by using Amazon\/Azure Storage Task for SSIS. Filter files using the wildcard (*) symbol.","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\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/","og_locale":"en_US","og_type":"article","og_title":"Filter by file size, name, date for Amazon \/ Azure Storage SSIS Task | ZappySys Blog","og_description":"Perform advanced file operations on Amazon Storage or Azure Storage by using Amazon\/Azure Storage Task for SSIS. Filter files using the wildcard (*) symbol.","og_url":"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2017-07-21T14:50:47+00:00","article_modified_time":"2019-02-15T14:23:24+00:00","og_image":[{"width":180,"height":180,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-tip.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\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/","url":"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/","name":"Filter by file size, name, date for Amazon \/ Azure Storage SSIS Task | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-tip.png","datePublished":"2017-07-21T14:50:47+00:00","dateModified":"2019-02-15T14:23:24+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Perform advanced file operations on Amazon Storage or Azure Storage by using Amazon\/Azure Storage Task for SSIS. Filter files using the wildcard (*) symbol.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-tip.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/06\/ssis-tip.png","width":180,"height":180},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/advanced-path-filtering-for-amazon-azure-storage-ssis-task\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Filter by file size, name, date for Amazon \/ Azure Storage SSIS Task"}]},{"@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\/1590"}],"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=1590"}],"version-history":[{"count":11,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1590\/revisions"}],"predecessor-version":[{"id":6371,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1590\/revisions\/6371"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/1897"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=1590"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=1590"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=1590"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}