{"id":344,"date":"2016-02-15T23:35:43","date_gmt":"2016-02-15T23:35:43","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=344"},"modified":"2017-10-02T14:05:49","modified_gmt":"2017-10-02T14:05:49","slug":"get-list-of-files-and-folders-in-ssis-for-looping","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/","title":{"rendered":"Get list of files in SSIS for Looping"},"content":{"rendered":"

Introduction<\/h2>\n

In this post you will learn how to use Advanced File System Task<\/a> to get list of files and folders into variable. Advanced File System Task<\/a> is significantly better than native SSIS File System Task<\/a><\/p>\n

How to get list of files in SSIS<\/h2>\n

Most simplest way to get list of files in SSIS is use wildcard pattern as below screenshot. You can also use Scan Recursive option to include files inside child folders. For advanced filtering options use Filter and Sorting Tab<\/strong><\/p>\n

    \n
  1. Download and install SSIS PowerPack from here<\/a><\/li>\n
  2. From toolbox of SSIS designer drag ZS Advanced File System Task<\/a><\/li>\n
  3. Double click Advanced File System task to configure it<\/li>\n
  4. Select Action as [Get file list as ADO.net DataTable]<\/li>\n
  5. Now select files you want to get using wildcard pattern (e.g. c:\\ssis\\file*.txt)<\/li>\n
  6. In the result variable select variable or create new variable with Object datatype. This variable will host file list in the form of ADO.net DataTable<\/li>\n<\/ol>\n
    \"SSIS<\/a>

    SSIS Advanced File System Task – get file list as ADO.net Recordset into variable<\/p><\/div>\n

    \"Loop<\/a>

    Loop through files in SSIS using ForEach Loop Task (use ADO.net Recordset variable)<\/p><\/div>\n

    \"Variable<\/a>

    Variable Mappings for ForEach Loop Task – Loop through files in a folder using SSIS<\/p><\/div>\n

    Get files\u00a0modified in last N days<\/h3>\n

    To get files modified in last N days you can use Advanced File System Task.<\/p>\n

      \n
    1. Download and install SSIS PowerPack from here<\/a><\/li>\n
    2. From toolbox of SSIS designer drag ZS Advanced File System Task<\/a><\/li>\n
    3. Double click Advanced File System task to configure it<\/li>\n
    4. Select Action as [Get file list as ADO.net DataTable]<\/li>\n
    5. Now select files you want to get using wildcard pattern (e.g. c:\\ssis\\file*.txt)<\/li>\n
    6. Go to Filter and Sorting<\/strong> tab. In the Where Field enter expression LastEditInDays < 5<\/strong> This will give you list of files modified in last 5 days. Here are some more examples.\n
      \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nExamples:\r\n\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\r\nAgeInDays < 5  \/\/Get files created in last 5 days\r\nLastEditInDays > 3 \/\/Get files modified before 3 days\r\nAgeInDays > 10 AND LastEditInDays < 3  \/\/Get files created before 10 days and modified in last 3 days\r\nSize < 1024  \/\/Get files where File size is less than 1024 bytes\r\nCreationDate BETWEEN #2015-01-01# AND #2015-01-01#   \r\nName IN ('File1.txt', 'Download.msi')\r\nFullPath LIKE 'c:\\temp\\da*')\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\r\n\r\n<\/pre>\n
      \"SSIS<\/a>
      SSIS Advanced File System Task – Get files modified in last N days<\/p><\/div><\/li>\n<\/ol>\n
      Get files\u00a0with specific size<\/h3>\n
      To get files with specific size you can use Where Expression<\/strong> on Size attribute (Size is in Bytes)<\/p>\n
        \n
      1. Download and install SSIS PowerPack from here<\/a><\/li>\n
      2. From toolbox of SSIS designer drag ZS Advanced File System Task<\/a><\/li>\n
      3. Double click Advanced File System task to configure it<\/li>\n
      4. Select Action as [Get file list as ADO.net DataTable]<\/li>\n
      5. Now select files you want to get using wildcard pattern (e.g. c:\\ssis\\file*.txt)<\/li>\n
      6. Go to Filter and Sorting<\/strong> tab. In the Where Field enter expression Size < 1024<\/strong> This will give you list of files which are less than 1024 bytes (e.g 1KB).\n
        Size < 1024\r\n<\/pre>\n<\/li>\n<\/ol>\n
        Get files with Include\/Exclude pattern (Use Regular Expression)<\/h3>\n
        Sometimes you have need to list files with specific name patterns which is hard to get using simple wildcard then use Advanced Regular Expression option. For this use wildcard in Path (e.g. c:\\ssis\\*.*) and then in the Filter and Sorting<\/strong> tab use Include \/ Exclude Regular Expressions (e.g. RegX) for file name pattern.<\/p>\n
        For Example if you want to include all files containing *MMDD.* pattern but not *.zip file or *.gz then use below expressions<\/p>\n
        Include ==>  ^w+_\\d\\d\\d\\d.\\w+$   \r\nExclude ==>  \\.zip|\\.gz \r\n<\/pre>\n
        How to get list of folders in SSIS (list directories)<\/h2>\n
        Coming soon<\/p>\n
        Loop through files or folders in SSIS using ForEachLoop Task<\/h2>\n
        Coming soon<\/p>\n
        Read Recordset and load into database (e.g. SQL Server Table)<\/h2>\n
        Coming soon<\/p>\n
        Conclusion<\/h2>\n
        Microsoft native file system task has very limited features and many scenarios require you to write C# \/ VB.net Script. But using ZS Advanced File System Task<\/a> you can eliminate need for scripting and use simple drag and drop approach. To use this task Download SSIS PowerPack<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
        Introduction In this post you will learn how to use Advanced File System Task to get list of files and folders into variable. Advanced File System Task is significantly better than native SSIS File System Task How to get list of files in SSIS Most simplest way to get list of files in SSIS is […]<\/p>\n","protected":false},"author":1,"featured_media":347,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47],"tags":[24,12],"yoast_head":"\r\nGet list of files in SSIS for Looping | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"How to get list of files in a folder without SSIS Script. Get files modified in last N days. Get files with specific size. Loop through files in SSIS.\" \/>\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\/get-list-of-files-and-folders-in-ssis-for-looping\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Get list of files in SSIS for Looping | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"How to get list of files in a folder without SSIS Script. Get files modified in last N days. Get files with specific size. Loop through files in SSIS.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/\" \/>\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=\"2016-02-15T23:35:43+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2017-10-02T14:05:49+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-get-file-list-as-recordset-for-looping.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"576\" \/>\r\n\t<meta property=\"og:image:height\" content=\"485\" \/>\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=\"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\/get-list-of-files-and-folders-in-ssis-for-looping\/\",\"url\":\"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/\",\"name\":\"Get list of files in SSIS for Looping | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-get-file-list-as-recordset-for-looping.png\",\"datePublished\":\"2016-02-15T23:35:43+00:00\",\"dateModified\":\"2017-10-02T14:05:49+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"How to get list of files in a folder without SSIS Script. Get files modified in last N days. Get files with specific size. Loop through files in SSIS.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-get-file-list-as-recordset-for-looping.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-get-file-list-as-recordset-for-looping.png\",\"width\":576,\"height\":485,\"caption\":\"SSIS Advanced File System Task - get file list as ADO.net Recordset into variable\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Get list of files in SSIS for Looping\"}]},{\"@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":"Get list of files in SSIS for Looping | ZappySys Blog","description":"How to get list of files in a folder without SSIS Script. Get files modified in last N days. Get files with specific size. Loop through files in SSIS.","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\/get-list-of-files-and-folders-in-ssis-for-looping\/","og_locale":"en_US","og_type":"article","og_title":"Get list of files in SSIS for Looping | ZappySys Blog","og_description":"How to get list of files in a folder without SSIS Script. Get files modified in last N days. Get files with specific size. Loop through files in SSIS.","og_url":"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2016-02-15T23:35:43+00:00","article_modified_time":"2017-10-02T14:05:49+00:00","og_image":[{"width":576,"height":485,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-get-file-list-as-recordset-for-looping.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":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/","url":"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/","name":"Get list of files in SSIS for Looping | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-get-file-list-as-recordset-for-looping.png","datePublished":"2016-02-15T23:35:43+00:00","dateModified":"2017-10-02T14:05:49+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"How to get list of files in a folder without SSIS Script. Get files modified in last N days. Get files with specific size. Loop through files in SSIS.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-get-file-list-as-recordset-for-looping.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/02\/ssis-get-file-list-as-recordset-for-looping.png","width":576,"height":485,"caption":"SSIS Advanced File System Task - get file list as ADO.net Recordset into variable"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/get-list-of-files-and-folders-in-ssis-for-looping\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Get list of files in SSIS for Looping"}]},{"@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\/344"}],"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=344"}],"version-history":[{"count":3,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/344\/revisions"}],"predecessor-version":[{"id":2055,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/344\/revisions\/2055"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/347"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=344"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=344"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=344"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}