Get dynamic file name using foreach loop container SSIS

When we are working with file in SSIS we need to set the file name dynamically. We can’t hard coded the file name. If in a folder we have multiple file and want to load all file to database in this case we need to get the file name. In this article we will learn how we get the dynamic file name.

Let’s see the example.

TestData folder I have some file. I want to get the file name dynamically.

Open SSDT.

For enumerator we need to take foreach loop container.

  

Double click on the foreach loop container.

Foreach loop editor will be open.

Go to collection tab.

Select foreach file enumerator.

Now we need to select the folder where stored

Write the file extension and select the retrieve file name. If you want to fetch the all file which is in sub folders the select Travers sub folder option.

Now we need to create a variable which store the file name.

Creating a variable name as filename which store the file name.

Now I am taking script task inside foreach loop container to display the file name.

Double click on the script task.

 

Select the variable name.

Click on the edit script button and write the C# script to display the file name.

Save and close it. Now package is ready to run. Now I am running the package.

Got the expected output.