I have placed the script task inside the FEL. I have 3 package variables named Directory, FilePrefix, and OutputFileName and they all are strings. In script task editor, the read only variables are Directory and FilePrefix and the read write variables is OutputFileName.

The code in the script:

Dim sFinalFileNameAndPath as String

Dim sDirectory as String

Dim sFilePrefix as String

sDirectory = Dts.Variables("Directory").Value.ToString

If Not (sDirectory.EndsWith("\")) Then

sDirectory = sDirectory + "\"

End If

sFilePrefix = Dts.Variables("FilePrefix").Value.ToString

sFinalFileNameAndPath = sDirectory + sFilePrefix + ".xlsx"

Dts.Variables("OutputFileName").Value = sFinalFileNameAndPath

Dts.TaskResult = ScriptResults.Success

P/S: I don't have any idea how to get those outputs from each text file (flat file source) written to each excel file (excel destination).