By:Nat Sundar || Related Tips:More > Integration Services Control Flow Transformations

Problem

I have a requirement to search for multiple file extensions in a folder. However, in SQL Server Integration Services (SSIS) there is a limitation that the "Foreach File Enumerator" can filter only one type of file. Are there any workarounds to search for multiple file extensions?

Solution

Unfortunately it's a limitation of SSIS and the "Foreach File Enumerator" supports only one type of file.


Search for Multiple File Extenstions with the SSIS Foreach File Enumerator

However this limitation can be overcome with a simple script task and a few lines of C# or VB.NET code. In this tip, I will walk through the solution in detail.

Solution Overview

I developed a simple package to explain the solution in detail. This package has a script task and this script task will loop thru the given folder for multiple file extensions. In addition, the script task will build an array to list the identified files. The details of the files will be stored as an object variable.

A "Foreach Loop" container has been created with "variable Enumerator". This will help us loop thru each lookup file. To showcase the functionality, I have simply added a script task to print the name of the file. So when you run this package, it is expected to search for files with many extensions and print the full path and name.

Solution Details

The below picture represents the package structure at a very high level.


Search for Multiple File Extenstions with the SSIS Foreach File Enumerator

The below variables have been defined in the package:

DataFolder - Location of the folder where the SSIS package will look for files FileFilters - Multiple file extensions or patterns separated by a pipe symbol FileList - Internal variable used by the SSIS package to store the list of files (object variable) FileName - Internal variable used by the SSIS package to store the value of each file name
Search for Multiple File Extenstions with the SSIS Foreach File Enumerator
SSIS Script Task

The script task will search for the files in the data folder based on the file filters. The variables "DataFolder" and "FileFilters" have been defined as ReadOnly variables for the script task. The script task will search for files and store the list of files in the object variable "FileList". Hence that variable "FileList" has been defined as a ReadWrite variable.

The below picture represents the variable configurations for the script task.


Search for Multiple File Extenstions with the SSIS Foreach File Enumerator
Code Inside the SSIS Script Task

It is mandatory to refer to the below namespaces. This will help us to access the directory and file related functions with ease.

using System.Collections;
using System.IO;

The below represents the actual code inside the script task. The code has been written using C#. The same functionality can be achieved by using VB.NET.

// TODO: Add your code here
string DataFolder, FileFilters;
DataFolder = Dts.Variables["User::DataFolder"].Value.ToString();
FileFilters = Dts.Variables["User::FileFilters"].Value.ToString();
// ArrayList will store multiple file names
ArrayList LookupFiles = new ArrayList();
// Delimited file filteres will be extracted and stored as a individual item in a string array
string[] strFileFilters = FileFilters.Split('|');
// for each filter find matching file names
foreach (string FileFilter in strFileFilters)
{
// Adding found files in the array list for each file filter
LookupFiles.AddRange(Directory.GetFiles(DataFolder, FileFilter, System.IO.Searchoption.AllDirectories));
}
//copy the values of multiple files to the object variable.
Dts.Variables["User::FileList"].value = LookupFiles;
Dts.TaskResult = (int)ScriptResults.Success;

Here is a screenshot of the code as well.


Search for Multiple File Extenstions with the SSIS Foreach File Enumerator

In the first section of the code we are storing the values of the DataFolder and the FileFilters to local variables. As the fileFilter has multiple extensions, all the individual items have to be stored as an array using the split method. From this array the individual items can be looped thru using a foreach loop. Now the individual files can be searched using the Directory.GetFiles method. This method accepts a folder location, file pattern and an option as parameters. This method returns the available files as a string array. This string array can be added to an ArrayList. Finally the array list can be stored in the SSIS object variable. In our example the array list will be stored in the FileList object variable.

Foreach From Variable Enumerator

Now a "Foreach Container" has been used to extract the name of the individual file from the FileList object variable. The enumerator has been set as "Foreach From Variable Enumerator". The object variable FileList has been set in the Enumerator configuration. The "FileName" variable has been set in the variable mappings section. This foreach container will loop thru each item in the FileList object variable and assign the individual file name to the FileName variable.

The below pictures represent the configurations for the "Foreach Loop" container.


Search for Multiple File Extenstions with the SSIS Foreach File Enumerator
Search for Multiple File Extenstions with the SSIS Foreach File Enumerator
Script Task Inside the Foreach Container

A script task has been placed inside the foreach container to print the name of the individual file. The variable FileName will be passed to the script task. The script task will be able to print the filename with the full path using the messagebox.show function.

The below picture represents the configurations for the script task (Inside the Foreach Loop).


Search for Multiple File Extenstions with the SSIS Foreach File Enumerator

The below picture represents the code inside the script task to print the file name.


Search for Multiple File Extenstions with the SSIS Foreach File Enumerator
Execution Results

Now the package can be executed to see the results. The below pictures confirm the solution has correctly identified the files with csv and txt extensions in the given folder.


Search for Multiple File Extenstions with the SSIS Foreach File Enumerator
Search for Multiple File Extenstions with the SSIS Foreach File Enumerator
Summary

I recommend trying this option in your development environment with a reasonable number of data files. If satisfactory, then deploy and test the solution in a test environment.

Next Steps Read more about the Script Task here Other SSIS Tips can be foundhere Last Update: 2/14/2017

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: C#SQLSQL Server
分页:12
转载请注明
本文标题:Search for Multiple File Extenstions with the SSIS Foreach File Enumerator
本站链接:http://www.codesec.net/view/534619.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(108)