Error when using Int or String Datatype Variable but Works with Object Datatype

Question

Error when using Int or String Datatype Variable but Works with Object Datatype

SSIS.COM

SSCommitted

Points: 1735
More actions
July 6, 2010 at 6:10 am

#147912

My scenario is I am assigning a value to a package variable in execute sql task which is in a for each loop container. Set @new=1. The variable type I have defined is Int32. It gives me the following error when I use Int Datatype or string where I change my set statement to @new='1'. In the parameter mapping @new is set to Output.
But the for each loop works fine if I select object datatype Variable. But the problem is I am defining an expression for the prcedence constraint after the for each loop which points to another task if @new=1. But it looks like I cannot use the object variable type in expression editor for precedenc econstraint.
Any sugestions.....Here is the error I got
Error: 0xC001F009 at filecheck: The type of the value being assigned to variable "User::New" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "sp_sample" failed with the following error: "The type of the value being assigned to variable "User::New" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task

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

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

Dave Morrison SSCrazy Points: 2077 More actions · Answer 1

Hi,

I'm pretty sure this is because, even if you set the ResultSet option to single row, your output can potentially still contain multiple colums (values) i.e. if you did a select for example in your sql statement. The best thing to do is put it into a object variable and the "shred" this object variable using a foreach container into another single variable of the int32 type.

There's a whole bunch of articals on variable shredding available on here and on the web generally but if you want some more assitance please just ask 🙂

Thanks

SSIS.COM SSCommitted Points: 1735 More actions · Answer 2

I am not using any result set here. That option is set to none for my execute sql task. And I am just assigning a value to a variable in my sql statement.

Dave Morrison SSCrazy Points: 2077 More actions · Answer 3

SSIS.COM (7/6/2010)
I am not using any result set here. That option is set to none for my execute sql task. And I am just assigning a value to a variable in my sql statement.

sorry I misunderstood.

which datatype are you setting in the parameter mapping?

The reason it works with the object type is because it's effectivly a variant type.

Have you tried as a test setting the package variable to string and the parameter mapping datatype as varchar. also ensure that the variable within the sql statement matches this type also

SSIS.COM SSCommitted Points: 1735 More actions · Answer 4

I have tried that Dave with string in package variable and parameter mapping and as varchar in sql statement.

But I get the same error..I wonder what went wrong..