SQL - Build a comma separated list of string values which can be used with an 'IN' statement

This is what I used to take a list of values and create one string to use with an 'IN' statement.

 Declare @Val1 Varchar(20) = 'V1'
 Declare @Val2 Varchar(20) = NULL
 Declare @Val3 Varchar(20) = 'V3'
 Declare @Val4 Varchar(20) = 'V4'


 Declare @Val_List Varchar(8000)
 Declare @Select_SQL Varchar(8000)

 Set @Val_List =
Case When @Val1 IS NULL Then '' Else + '''' + @Val1 + '''' End  
    + Case When @Val2 IS NULL Then '' Else + ',' + '''' + @Val2 + '''' End  
    + Case When @Val3 IS NULL Then '' Else + ',' + '''' + @Val3 + '''' End  
    + Case When @Val4 IS NULL Then '' Else + ',' + '''' + @Val4 + '''' End;
   
   
 Set @Select_SQL = 'Select * From Table_Name Where Val In (' + @Val_List + ')';

 Print @Select_SQL;

-- This is the result:

Select * From Table_Name Where Val In ('V1','V3','V4')

Error Cannot open the datafile \\server\share\dir\filename.csv when reading from a CSV file in SSIS

I am getting this error when a Flat File connection tries to read a CSV file. The path to the file, including the file name is correct.

The import works fine when the path to the file is hard-coded. It does NOT however work (with the above error), when I set the Flat File Connection Manager Connection String Property in a VB Script Task.

I am using this to set the Connection String and it does get set correctly. A Variable (strFileLocationFileName contains the File Path and File Name).

ConnectionManager.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileLocationFileName & ";Extended Properties=""text;HDR=YES;FMT=Delimited"";"

The issue was resolved when I removed this Script Task and instead assigned the File Name and File Location to the Flat File Connection Manager Connection String as an Expression using the strFileLocationFileName Variable.