SSIS Error The type of the value (DBNull) being assigned to variable "User::FileName" differs from the current variable type (String)

Transact-SQL
6

I was going to mention that - you can't put a full resultset into a string variable. It would have to be defined as an object - and then you can use a foreach loop to enumerate the rows in the ADO resultset.

Or - you can use a single row and map each column to a variable.

Or - you can set input and output variables and assign the value in the query. For this - you would add a variable as a parameter and set the parameter as output and the query something like:

DECLARE @somevar varchar(20) = '';
SELECT @somevar = filename FROM table1;
SET ? = @somevar;