SSIS 2012 Execute SQL Task with resultset to populate user variables produces DBNull error.

Question

SSIS 2012 Execute SQL Task with resultset to populate user variables produces DBNull error.

Scott Clark-275269

Ten Centuries

Points: 1221
More actions
June 17, 2014 at 10:27 pm

#294703

I am experiencing an unexplainable behavior with the Execute SQL Task control in SSIS 2012. The following is a description of how to simulate the issue I will attempt to describe:
1. Create a package and add two variables User::varTest1 and User::varTest2 both of String type with default value of "Select GetDate()" for each, have shown this to not matter as the same behavior occurs when using a fixed string value or empty string value.
2. Add a new Execute SQL Task to the control flow.
3. Configure an OLE DB connection.
4. Set SQLSourceType = "Direct Input"
5. Set the ResultSet property of the task to "Single Row"
6. In the ResultSet tab add two results as follows:
Result Name: returnvalue1, Variable Name: User::varTest1
Result Name: returnvalue2, Variable Name: User::varTest2
7. Set an expression for the SqlStatementSource property with a string value of "Select 'Test' returnvalue1, 'Testing' returnvalue2'"
The idea is that the source would be dynamically set in order to run a t-sql statement which would have dynamic values for database name or object that would be created at runtime and then executed to set the user variable values from its resultset. Instead what occurs is that a DBNull error occurs.
I am not sure if anyone else has experienced this behavior performing similiar actions with the Execute SQL Task or not. Any help would be appreciated. The exact message is as follows:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "varRestoreScript": "The type of the value (DBNull) being assigned to variable "User::varRestoreScript" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.
".

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

What if you put a dummy select statement in the Execute SQL Task first. Something like "SELECT '1','2'". This dummy statement will be replaced with the expression anyway.

The User::varRestoreScript variable, is this User::varTest1 or User::varTest2 from your example?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Scott Clark-275269 Ten Centuries Points: 1221 More actions · Answer 2

User::varRestoreScript is the first return value. And even with the a dummy select the same result occurs. I have narrowed the issue down to the T-SQL Statement structure itself.

The following works just fine within the execute sql task control as long as no resultset is configured for return:

"Declare @dynamicSQL nvarchar(max)

Select @dynamicSQL = name

From sys.databases

Where name = 'master'

Select atest_var1=@dynamicSQL, atest_var2='static'"

I have tried various iterations of the script above with no success. However, if I use the following derivative of it the task completes successfully and variables are set as expected.

"Select atest_var1=name, atest_var2='static'

From sys.databases

Where name = 'master'

"

I have attached the solution for the test package I used to recreate this issue and debug from.

Scott Clark-275269 Ten Centuries Points: 1221 More actions · Answer 3

Found the issue.

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 :(.

Now to work around this limitation :(.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

Scott Clark-275269 (6/19/2014)
Found the issue.
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 :(.
Now to work around this limitation :(.

Glad you found the issue.

Maybe stored procedures are an option?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

tania_drt SSC Rookie Points: 29 More actions · Answer 5

Thanks for your help. I also tried creating a stored procedure, but the problem persists in this version of SQL Server.

dbaRian Valued Member Points: 55 More actions · Answer 6

What is the data type of the variable assigned in ResultSet of Execute SQL Task? Set the data type to object.