How do I use a wild card with alphabets of file name in SSIS foreach loop container

user_12197772 0
Viewed 385

The for each loop editor in SSIS comes with an option to pick the loop enumerator as Foreach File Enumerator under which I am trying to use the wild card to avoid an additional script task but the file is not being read. I have list of files that ends with extension .csv so if I do *.csv it'll read all files but I want the file that ends only with "_names.csv", however the package is not able to read any files if I do that. I'm attaching the screenshot of my Foreach loop editor settings. Any ideas what am I doing wrong? I tested this wildcard in PowerShell using ls -file *_names.csv and I'm getting the filtered list of files.

enter image description here

ssis foreach
1 Answers

It works for me, so perhaps something else is amiss with your situation.

Setup

On disk, I have 3 files in my Input folder, only one matches the expected criteria

  • C:\ssisdata\so\input\foo_names.csv
  • C:\ssisdata\so\input\norms.csv
  • C:\ssisdata\so\input\no_names.csvv

My SSIS package has 3 variables, all of type string.

  • CurrentFileName - This is populated per iteration of my ForEach Loop
  • FileNameFilter - This is *_names.csv
  • FolderName - This points to C:\ssisdata\so\input

enter image description here

enter image description here

The script task is just a trivial bit of code that prints the value of variables to the output/results tab. The namespace will vary based on randomness of clicking create Script Task

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_6da5625c3b1a4e13988a4a2c68531e4d
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool fireAgain = false;
            foreach (var item in Dts.Variables)
            {
                Dts.Events.FireInformation(0, "SCR Echo Back", string.Format("{0}:{1} -> {2}", item.Namespace, item.Name, item.Value), "", 0, ref fireAgain);
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
    }
}

Test cases

The first case I used was a simple *.csv to ensure yes, I am seeing files. This eliminates things like permissions or broken path issues. In the event that it's a path/permissions issue, you would see the following Warning

Warning: 0x8001C004 at FELC name csv: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

A thing to note about the *.csv iteration, it picked up the no_names.csvv Why? because it was designed for 8.3 names. No citation given as I'm too lazy

I then used N*.csv as my next iteration. It picked up norms.csv and no_names.csvv as expected.

Finally, I used the original *_names.csv as my input filter and it correctly picks up foo_names.csv

Output

SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\so_69215306.dtsx" starting.
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:FileNameFilter -> *.csv
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:FolderName -> C:\ssisdata\so\input
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:CurrentFileName -> C:\ssisdata\so\input\foo_names.csv
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:CurrentFileName -> C:\ssisdata\so\input\norms.csv
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:CurrentFileName -> C:\ssisdata\so\input\no_names.csvv
SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\so_69215306.dtsx" finished: Success.

SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\so_69215306.dtsx" starting.
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:FileNameFilter -> N*.csv
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:FolderName -> C:\ssisdata\so\input
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:CurrentFileName -> C:\ssisdata\so\input\norms.csv
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:CurrentFileName -> C:\ssisdata\so\input\no_names.csvv
SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\so_69215306.dtsx" finished: Success.
The program '[49988] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\so_69215306.dtsx" starting.
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:FileNameFilter -> *_names.csv
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:FolderName -> C:\ssisdata\so\input
Information: 0x0 at SCR Echo Back, SCR Echo Back: User:CurrentFileName -> C:\ssisdata\so\input\foo_names.csv
SSIS package "C:\Users\bfellows\source\repos\SO_Trash\SO_Trash\so_69215306.dtsx" finished: Success.
user_1819650
Related