SSIS - Get list file names and store into variable object-CodePudding

Hi I have csv file into one location, I am using a package that has a forearch loop container, get I put foreach file enumeador, and checked name and extension, then I went to variable mapping and put my filename variable. Then I have a filenamelist variable as object datatype, what tool I can use to fill my filenamelist (object) with each filename?

path

users/abc.csv users/azd.csv

get filenames into single variable abc.csv, azd.csv, ...

Regards

CodePudding user response：

That's not how the Foreach File Enumerator works

Then I have a filenamelist variable as object datatype, what tool I can use to fill my filenamelist (object) with each filename?

In the Variable mapping tab of the Foreach Enumerator, you provide a variable that will hold the current value of the enumerator.

In your sample, you have two files in the users folder: abc.csv and azd.csv

The ForEach (File) Enumerator has the smarts to ask the operating system what files match the given file mask (*.csv) that are in the source folder (users). That full list is not available to you as the assumption is you need to perform a task on each item in sequence.

You likely want an SSIS Variable of type String called CurrentFileName which you would then use where you have filenamelist

The first iteration through, the value of CurrentFileName would be users/abc.csv and you would then add Tasks into the container e.g. a data flow task to load data and then a successor of File System Task to archive the file to a different folder.

The next iteration through, the value of CurrentFileName becomes azd.csv and all the same tasks would be applied but with a new value.

A common misstep is wiring up the value of CurrentFileName into the appropriate Connection Manager so that the source/target file changes in a Data Flow.

No, I really wanted a list

If you really need to store a list of values, then the datatype of object is correct. What you're then looking for is to write your own Script Task (C# or VB.NET) that generates the list of files and assigns it to the Variable's value. Assuming you have selected User::filenamelist as a read/write variable, that code is approximately

// TODO: replace users with actual path
string directoryName = @"users";

// TODO: is this a valid filter?
string fileExtension = "*.csv";

// Instantiate the directory object
System.IO.DirectoryInfo directoryInfo = new System.IO.DirectoryInfo(directoryName);

// Change the SearchOption to AllDirectories if you need to search subfolders
System.IO.FileInfo[] legacyArray = directoryInfo.GetFiles(fileExtension, System.IO.SearchOption.TopDirectoryOnly);

// Assuming all is well, assign back to the SSIS scoped variable
// This is case sensitive so be wary
// Ensure you assign to the Value property as shown
Dts.Variables["User::filenamelist"].Value = legacyArray;

The Task completes, the SSIS Variable filenamelist is populated with values - now what? You can't really do much with a object data type in SSIS beyond ensuring it's not null. However, you can use that variable as the source for a Foreach Enumerator but this time, instead of File, you'd change it to...something with Variable in the title, maybe Variable Enumerator?