no luck w/ execute sql task and assigning variable

Hello,

I've asked this question before and I've read the answers before and I still cannot get it to work. My task is simple, I want to use the execute sql task container to grab a value from a database and put it in a variable. I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

select output_location as output_location
from script_master

Result Set is set up like this:

Result Name: output_location ; Variable Name: User::output_location

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "output_location": "The type of the value being assigned to variable "User::output_location" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

".

I don't know what I'm doing wrong, I've followed all the instructions exactly on how to populate a variable in this container. My variable is set up as a string, if I change it to object I can get it to work. I think this is because the object is allowing nulls. I really believe that the variable is not populating and that is why I'm getting errors.

Please help. If you could provide step by step example's that would really make my day.

Thanks,

Phil

First, your result set name should be "0".
Second, does your query return more than one row? If not, make sure that your result set is set to "single row." If your query returns more than one row, then you'll need to make sure User::output_location is an OBJECT datatype. If you only have one row being returned, then User::output_location can be set to the datatype of the query returned value.|||

How many rows does the query retreive? are you sure?

What is the data type of the column you are trying to query?

What is the data type of Variable?

|||Hi Phil,

You can modify your select statement to handle nulls:

select ISNULL(output_location,'') as output_location
from script_master

I partly disagree with Phil (Brammer) that you should set the Result set name to "0". That depends... if you're using an ADO.NET connection manager "0" would work but if you're using an OLEDb connection manager then the column name of the result (in this case, "output_location") should be used.|||

I just played around with this some more. I made the sql statement:

select count(output_location) as output_location

from script_master

and the result is 1. I also changed the variable to int16 and IT WORKED!! But, alas, that's not what I want I actually want the value of output_location which is a nvarchar column in the database. As soon as I changed the variable from int16 back to string and changed count(output_location) back to just output_location, I got the same error as before.

Could it have anything to do with my result of output_location is returning a string like this:

\\usilchfp01\userdata\ptackett\SSIS\test.xls

Thanks for the quick replies,
Phil

|||

Jon Limjap wrote:

Hi Phil,

You can modify your select statement to handle nulls:

select ISNULL(output_location,'') as output_location
from script_master

I partly disagree with Phil (Brammer) that you should set the Result set name to "0". That depends... if you're using an ADO.NET connection manager "0" would work but if you're using an OLEDb connection manager then the column name of the result (in this case, "output_location") should be used.

OLE DB uses numeric parameter names. You have that backwards, but the point is still valid.|||

tackett wrote:

Could it have anything to do with my result of output_location is returning a string like this:

\\usilchfp01\userdata\ptackett\SSIS\test.xls

Hmmm, not sure. Perhaps you can chnage the value in the table to a more simple and test it.

|||

Ok, I tried the:

select isnull(output_location,'') as output_location

from script_master

Same result.

I also tried it with a where clause, figuring it didn't like the fact I didn't have one before:

select isnull(output_location,'') as output_location

from script_master

where 1 = 1

That also didn't work.

I've tried using the "0" as the result name as well as "output_location" both do not work.

I appreciate the feedback so far. Any other ideas?

Thanks,

Phil

|||Oops sorry didn't see the post above.

I think the \\ might be wrecking the variable somehow. Strings in SSIS use C-style escaping, so having backslashes allover the place might be emitting some illegal escape characters of some sort. That's just a guess.

You might want to try this approach: assign another variable as an object to the Result of your query. Then have your original string variable's EvaluateExpression property set to True. In the expression cast the object to a string with the expression "<DT_STR 30,1252> @.[User::objOutputLocation]"

(I'm not sure with the property name and the expression... I'm not on my SSIS machine right now)|||

I just updated it from to be:

update script_master

set output_location = 'try this'

so it's now "try this" as a string. But that didn't work either.

Phil

|||

tackett wrote:

I just updated it from to be:

update script_master

set output_location = 'try this'

so it's now "try this" as a string. But that didn't work either.

Phil

What's the column type of output_location? varchar? nvarchar?|||nvarchar|||nvarchar(max) actually|||

Well, I just figured it out. I changed my column from nvarchar(max) to nvarchar(500) and it works fine. What the *&^$? It also works if the column is nvarchar(max) and you cast or convert it along the way. Oh well, I learned this the hard way.

Thanks,

Phil

|||Ouch. I think that's it. It's possible that SSIS strings don't handle unicode strings that are that wide (specifically because nvarchars are twice the size of varchars).

If you can try to narrow your nvarchar definition (perhaps nvarchar(2000) would suffice) then try again, I think the problem will be solved. Otherwise you can try out what I posted above (the one that uses expressions).

HTHsql