String Result Set

Question

String Result Set

nomen

Old Hand

Points: 356
More actions
April 21, 2010 at 1:17 pm

#146594

I am using SQL Server 2005 SP3 on Windows Server 2003.
I have an ExecuteSQL task with the following SQL statement:
SELECT [EMailCC] as MessageCC
FROM A_FacilityLookup
WHERE [FacilityAbbr] = ?
The field is defined as varchar(256). I have the output mapped in the ResultSet tab of the task to a variable of the datatype string. The name of the variable is identical to the alias in the statement. The ResultSet property of the task is set to SingleRow.
When the task executes, I get an error message: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)".
If I modify the statement to CONVERT the field to varchar(256), I get an error: "An error occurred while extracting the result into a variable".
I have done this type of thing before with numeric results and have had no problems. This is the first time I have tried this with a string result. According to BOL, I should be able to do this as long as the result and the variable are of compatible datatypes.
Can someone offer some insight as to what is going on?
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

I have had issues before with string variables and SSIS thinking it was a number.

Most of the type these happen when I create the variable and change the type to string later on (as DT_I4 is the default datatype). For some reason, SSIS still thinks it is a number.

So, how do I solve it? By simply re-creating the variable and making sure it has the correct data type from the beginning. To be extra sure, type something in the variable (like HelloWorld). Normally that should be overwritten by your SQL statement. Try it out and let me know if it works.

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

nomen Old Hand Points: 356 More actions · Answer 2

I have tried and retried all your suggestions. I created the variable as a string type and put something in it when I created it. I have deleted and recreated the variable. I even tried changing the datatype to something other than string, saving the package, then changing the variable back to string. I tried setting up another variable from scratch. Nothing worked.

Thanks for the response.