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 + "\"
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).
Phil Parkin (3/28/2013)
I appreciate your response but do you think you could give me an example in your package? Do you think that ExcelFilePath will change dynamically for every iteration?
Sorry, don't have time to do that at the moment, but yes - if you use variables and expressions in the way I described it certainly should change with every iteration of the FEL.