Execute SQL Task -Full Result Set Datatype Mismatch Error

Question

I am creating an SSIS package which has an execute SQL task and it passes result set variable to a for each loop container. My Sql Query is:

Select distinct code from house where active=1 and campus='W'

I want the execute sql task to run this query and assign its results to a variable which is passed to a for each loop container which should loop through all the values in the result set.

But my execute sql task fails with error:

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

Now i have done my research and i have tried assigning the variable datatype Object but did not work. I tried using cast in my sql query and that also did not work.

Since my query returns multiple rows and one column, i am not sure how i can assign a datatype to the whole query?

Sample:

Code
AR
BN
CN

Huh? It sounds like you want a simple `join`. Why are you looping on the application side? — Gordon Linoff, Oct 28 '18 at 12:21
@GordonLinoff I am using for each loop container because i have data flow task in it. basically what i am trying to achieve is to make the for each loop loop through school houses one by one and then run a data flow task and then ultimately email an excel file to each head of house. I hope that clears up confusion — Jaspreet Saini, Oct 28 '18 at 12:24
Execute SQL task, ResultSet=Full result set, under result set you'll return that to variable of type Object. Then in the foreach loop, "Foreach ADO Enumerator", pick your variable of type Object, then under "Variable mappings" that's where you then assign "User:House". — Tim Mylott, Oct 29 '18 at 15:41

billinkc · Answer 1 · 2018-10-28T22:34:18.443

It sounds like you have a variety of issues in here.

Result Set

The first is in your Execute SQL Task and the need for agreement between the Result Set specification and the data type of the Variable(s) specified in the Result Set tab. If you specify Full Resultset, then the receiving object must be of type System::Object and you will only have 1 result set. The type of Connection Manager (ODBC/OLE/ADO) used will determine how you specify it but it's infinitely searchable on these fine forums.

The other two options are Single Row and XML. In 13 years of working with SSIS, I've never had cause to specify XML. That leaves us with Single Row. For a Single Row Result Set, you need to provide a variable for each column returned and it needs to be correctly typed.

To correct your issue, you need to declare a second variable. I usually call my rsObject (record set object) and then specify the data type as System.Object.

For Each Loop Container

Your For Each Loop Container will then be set with an Enumerator of "Foreach ADO Enumerator" and then the ADO object source variable will become "User::rsObject"

In the Variable Mappings, you'll specify your variable User::house to index 0.

Testing

Given a sample set of source source data, you can verify that you have your Execute SQL Task correctly assigning a result set to our object and the Foreach Loop Container is properly populating our variable.

SELECT DISTINCT
    code
FROM
(
    VALUES
        ('ABC', 1, 'w')
    ,   ('BCD', 1, 'w')
    ,   ('CDE', 0, 'w')
    ,   ('DEF', 1, 'w')
    ,   ('EFG', 1, 'x')
) house(code, active, campus)
WHERE
    active = 1
    AND campus = 'w';

If you change the value of campus from w to something that doesn't exist, like f then things will continue to work.

However, the error you're receiving can only be generated if the code is a NULL

Add one more entry to the VALUES collection like

    ,   (NULL, 1, 'w')

and when the For Each Loop Container hits that value, you will encounter the error you indicate

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

Now what?

SSIS variables cannot change their data type, unless they're of type Object (but that's not the solution here). The "problem" is that you cannot store a NULL value in an SSIS variable (unless it's of type object). Therefore you need to either exclude the rows that return a NULL (AND code IS NOT NULL) or you need to cast the NULL into sentinel/placeholder value as a substitute (SELECT DISTINCT ISNULL(code, '') AS code). If an empty string is a valid value, then you need to find something that isn't - "billinkcisthegreatestever10123432" is unlikely to exist in your set of codes but that might be a bit excessive.

Finally, think about renaming your SSIS variable from house to code. You might be able to keep things straight but some day you'll hand this code over to someone else for maintenance and you don't want to confuse them.

A picturesque answer https://stackoverflow.com/a/13976990/181965

My Query Returns data as following: Code BA CN JN So there is no Null value in the result. It is one column and 3 rows — Jaspreet Saini, Oct 29 '18 at 00:39

score 0 · Answer 2 · answered Oct 28 '18 at 12:42

0

the variable "User::house" is string , so , did you use it in result set?

answered Oct 28 '18 at 12:42

masoud

456
3
12

Yes i am using that in the result set. . I fixed this issue in another execute sql task which was returning single row set by formatting my query like this select email= cast((select email from staffemail) as varchar(500)) and this worked but i cannot do this for full result set query – Jaspreet Saini Oct 28 '18 at 12:50
if your resut set is full result set , you need declare varibale with object type and use it in result set , then declare a string var and then use it in foreach loop. i try to attach some screenshot in new a answer – masoud Nov 03 '18 at 11:07

score 0 · Answer 3 · answered Nov 03 '18 at 11:14

0

you need declare son "object" var for result set result set

then declare a string variable for every single Code from your result

For Each Loop Container good luck

answered Nov 03 '18 at 11:14