SSIS ExecuteSQL - Error Resultset trying to assign DBNULL to variable string

Question

SSIS ExecuteSQL - Error Resultset trying to assign DBNULL to variable string

ipisors 92539

SSC-Addicted

Points: 445
More actions
September 25, 2019 at 5:59 pm

#3683395
Ok ... I understand the concept that my ExecuteSQL task may not attempt to assign a DBNULL type to a String variable. What I don't understand is the "why" SSIS is seeing the result set as DBnull!!
And yes...I know that I may be able to solve the problem, maybe, partly, by just changing the variable to Object. But I don't want to close my eyes to the issue, I want to solve it.
Here is the execute sql SQLStatement property value:
```
declare @filename varchar(500) = ''
declare @filenames varchar(max) = ''
declare c cursor for
select
mf.[Missing Files]
from
Database.Schema.Table mf
open c
fetch next from c into @filename
while @@FETCH_STATUS=0
begin
set @filenames = isnull(@filenames,'') + @filename + ', '
fetch next from c into @filename
end
close c
deallocate c

select @filenames as 'MissingFiles'
```
PS .. While I am always open to someone making additional comments about opportunities for improvement in my code or other warnings, please resist the temptation to post a reply if your entire post is going to be brag-, mock-content, or a cursor lecture, with no assistance on question posted. I'm needing to learn whatever I need to learn in this case about datatypes from T-SQL going out through resultset in ssis. And open to learning something additional, too, if you so desire. 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ipisors 92539 SSC-Addicted Points: 445 More actions · Answer 1

After a few hours, I was worried that no one knew the answer to this - and that it might be an obscure and/or undocumented problem. In case you are curious to learn :: this is the solution, I discovered:

"SSIS 2012 still has a limitation on the String data type for variables. The use of the VarChar(Max) or NVarChar(Max) data type in the query was causing the failure. Changing this to a maximum size of 4000 resolved the issue. It hadn't dawned on me that I was trying to stuff a LOB into a conventional string data type :(."

(taken from another post).

Yikes ... what a misleading error message.

This reply was modified 5 years, 2 months ago by ipisors 92539.

Phil Parkin SSC Guru Points: 246963 More actions · Answer 2

Avoiding that cursor is pretty easy ... here's some much simpler code for you:

DECLARE @filenames VARCHAR(MAX) = (SELECT STRING_AGG(mf.[Missing Files],', ') FROM Database.Schema.Table mf where mf.[Missing Files] IS NOT NULL)

Edit: just seen your reply. STRING_AGG() isn't available in 2012 either, as far as I remember.

This reply was modified 5 years, 2 months ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ipisors 92539 SSC-Addicted Points: 445 More actions · Answer 3

Thanks, I was looking into for xml as well as string_agg but ditched for xml path because i couldn't figure out how to make it work with returning "just one" column, although I have a sneaking suspicion more effort would have yielded me the result. (in this case the expected operation will only involve a few rows so the cursor didn't worry me much. i did think of ado recordset looping + script task but found the sql one a little nicer).

but yes, string_agg is cool (seems overdue, years/versions-wise!) and i would use it if I were sure that my package deployment would be only later ss versions. 2016 is common for us where i work.

This reply was modified 5 years, 2 months ago by ipisors 92539.

Phil Parkin SSC Guru Points: 246963 More actions · Answer 4

Here's some sample code not using STRING_AGG() which may be of use:

CREATE TABLE #FileName
(
    FileName VARCHAR(200) NULL
);

INSERT #FileName
(
    FileName
)
VALUES
('file1.txt')
,('file2.txt');

DECLARE @filenames VARCHAR(MAX) = '';

SELECT @filenames = @filenames + CONCAT(fn.FileName, ', ')
FROM #FileName fn
WHERE fn.FileName IS NOT NULL;

SET @filenames = LEFT(@filenames, LEN(@filenames) - 1);

SELECT @filenames;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ipisors 92539 SSC-Addicted Points: 445 More actions · Answer 5

Wow. I shake my head in admiration. Not sure how many people have tapped into this variable-setting behavior, since a few google searches on the pro's of using Select to set variable values only led me to articles which claimed the variable took the value of the "last record in the result set" ... Which is , well, kind of correct, but I found no resources which alluded to the inherent recursive nature of what goes on while the variable's value is in-flux prior to that last record giving it it's final value................

Thank you. You have taught me a very useful thing method that ought to replace the ginormous collection of "for xml path" articles out there on the web, or at least 90% of them. Generally always using Set rather than Select (I like the readability), I would probably never have stumbled across this on my own.