How can I send out a script task email in SSIS that includes all files found in folder

Question

I am creating a package and the 1st step is it sending out an email(using script task, script language microsoft visual basic 2019) that tells the recipients what files have been received for processing. I am currently doing this with a ScriptTask task inside a Foreach Loop Container.

The email sends out successfully but it sends individual emails for each file it finds instead of sending one email that lists all files found. (I tried taking the ScriptTask out of the Foreach Loop container and that did not work)

, , ,

Answer 1

The program is doing as intended - the for each file loop container enumerates over each file that matches the search pattern and then you perform an action (send email) on that.

What you want to do is remove your For each file loop container.

Instead, your Script task is going to generate a list of files which you will then patch into the body of your email.

BooksOnLine doesn't have VB samples any more, it seems, but you want to use the System.IO.Directory.GetFiles static method.

' Logic here for building out the body of the email aka list of files
Dim filePath as string = "\\longservername\share\path\etc"
Dim filePattern as string = "*.txt"

' Whatever the VB syntax is for creating an array
var fileArray = System.IO.Directory.GetFiles(filePath, filePattern)

' again, I don't have VB handy but this will create a comma delimited list of all of our files
Dim allFiles as string  = string.join(fileArray, ",")

' at this point, we resume your code
e_email.Body = allFiles