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

Transact-SQL
1

This is my very simple SSIS project. I'm just trying to read a table which contains column FileName and place that value into a user variable also named FileName.
Variable FileName has Data Type of String

The SQL task gives this error:

[Execute SQL Task] Error: Executing the query "Select FileName from Table1" failed with the following error: "The type of the value (DBNull) being assigned to variable "User::FileName" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

2

Well - you haven't provided enough information for us to determine what you are trying to accomplish. Are you using a data flow, an execute sql task - something else?

How are you trying to assign the value to that variable?

Does that query return more than a single row - if so, how do you determine which row to be returned?

1 Like
3

To expand on this:

In an Execute SQL Task - you can define how the data is to be returned. Is there a resultset - if so, is it a single row, multiple rows or XML? If no rows are returned - is there an output parameter and how is that defined?

Depending on how this is defined will determine how you configure the parameter mapping and/or resultset tabs.

4

Sure @jeffw8713 The error occurs in the Execute SQL Task which has these settings:
Resultset Full resultset
ConnectionType: OLDE DB
Src Type Direct input
Bypass Prepare False
SQL Statement: Select FileName from Table1;

Parameter Mapping - there is none
Result Set: Result name is 0 Var name is User::FileName

5

... I just now changed Result Set to Single Row and now it gets past that error and moves onto the For Each Container loop

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;
7

@jeffw8713 I can't use Object for the variable because I use FileName in expressions in other variables to append tFileName to a folder path.
So var FileName is String
SQL Task is set to single row
Parm mappings: User::FileName output varchar Parameter Name is FileName2 -1
Result Set: 0 User::FileName
Expressions - none

The SQL Task gives this error:
"[Execute SQL Task] Error: Executing the query "Select FileName from Table1;" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

8

@jeffw8713 I was able to get it working. Thanks for your help.

I followed this article by Robert Sheldon here: It described all the steps I need for my use.