There are a few ways to do this. Both are workarounds but should do what you need.
1) Without using a script task.
a. Place an empty sequence container in the inner for each loop container.
b. Draw a precedence constraint on success.
c. Edit the expression to read SUBSTRING(@SheetName,LEN(@SheetName)-1,1)==”$”
2) With a script task in control flow
a. Add a script task.
b. Add a new variable “IsValidSheet” as a Boolean
c. Pass @SheetName as read only and IsValidSheet as Read Write to the script
d. Add code Dts.Variables(“IsValidSheet”).Value = Dts.Variables(“Sheetname”).Value.ToString().EndsWith(“$”)
3) In the data flow
a. Add a conditional split.
b. Use the formula from 1c
c. Ignore this case and just push through rows that fail this check (the default condition).
2 is likely the easiest to understand.
1 and 3 are basically the same trick in two different places.