Why does the SSIS Script Task fail to evaluate an expression?

Possible option:

You could use Execute SQL Task to fetch the record set instead of using a Data Flow Task.

SSIS 2012 package illustrating the example:

Let us assume that you have a table named dbo.FileNames in your data database with the some sample rows.

Create and populate table script:

CREATE TABLE dbo.FileNames(
    srcPath nvarchar(255) NULL,
    srcName nvarchar(60) NULL
);

GO

INSERT INTO dbo.FileNames (srcPath, srcName) VALUES
        ('C:\temp', 'File_1.txt')
    ,   ('C:\temp', 'File_2.txt')
    ,   ('C:\temp', 'File_3.txt')
    ;
GO

Configure the Control Flow tab as shown below. Place an Execute SQL Task to read the table data and store it in an object data type. Place a Foreach Loop Container after the Execute SQL Task and a Script Task within the Foreach Loop Container

Create the following variables. It is advisable to use the expression @[User::srcPath] + "\\" + @[User::srcFile] on the variable srcFull so that if the path is missing a trailing backslash, it will be added accordingly in between the path and file name.

Variable name  Scope         Data type  Expression
-------------- ------------- ---------- -----------
FileNames      SO_15072939   Object
srcFile        SO_15072939   String
srcFull        SO_15072939   String     @[User::srcPath] + "\\" + @[User::srcFile]
srcPath        SO_15072939   String

Configure the Execute SQL Task to run the below query against the connection manager to read the file path and name information.

SELECT srcPath, srcName FROM dbo.FileNames

The result set returned by the query should be stored into the variable FileNames of data type Object.

Configure the Foreach Loop Container to read the variable FileNames using Foreach ADO Enumerator.

Configure the two package variables to store the column values as the result set is being looped through.

Configure the Script Task Editor to read the variable srcFull

Add the below code to the Script Task.

Script task code in C# for SSIS 2008 and above:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion

namespace ST_f1d7b6ab42e24ad5b5531684ecdcae87
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            string filePath = Dts.Variables["User::srcFull"].Value.ToString();

            MessageBox.Show(filePath);

            Dts.TaskResult = File.Exists(filePath)
                                ? (int)ScriptResults.Success
                                : (int)ScriptResults.Failure;
        }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
    }
}

Executing the package will display the variable value in MessageBox.

The package will fail in this sample because the path C:\temp\File_1.txt does not exist.

Response to OP's comment:

I did what you're showing me here, seems that my problem is that since the User::srcFile variable is added as a ReadWriteVariables to the script task (since i need to do some stuff with it in the same script) it's kinda locked so the User::srcFull variable who's definition is @[User::srcPath] + @[User::srcFile] cannot be evaluated..

I modified the variable srcFull from ReadOnlyVariables to ReadWriteVariables on Script Task Editor and the expression still evaluated correctly. I created those three files in the sample input and the package displayed each path correctly in the message box and completed the execution successfully.

Adding the variables srcPath and srcFile to the ReadWriteVariables section of Script Task Editor will only result in a conflict because now both Script Task and Foreach Loop Container are trying to update the same variables.