Error when returning results of Execute SQL Task component to a string variable

 

hello,

 

in execute sql task, run following query:

 

declare @idlist varchar(max)

select @idlist = coalesce(@idlist + ', ', '') + id
from dbo.ids
where rownumber >= 1 , rownumber <= 5

select @idlist idlist

 

it returns this:  014023, 418738, 42108, 420109, 041592

 

the result set set single row, , have result name in result set set idlist.

 

the variable trying put result string , following error:

 

error: 0xc002f309 @ execute sql task 1, execute sql task: error occurred while assigning value variable "idlist": "the type of value being assigned variable "user::idlist" differs current variable type. variables may not change type during execution. variable types strict, except variables of type object.

 

i'm guessing doesn't return value varchar , variable string.  have tried change variable char , had no luck.  not sure next.

 

thanks in advance help.

 

d

i answered same query in sql central forum  but here go again...

 

ssis not able map varchar(max) string type. tested , when changed varchar(4000) able map without problem.

so if think return string gonna less 4000 can use varchar(4000) resolve otherwise suggest john rowan in sql central forum use full result set , loop through using foreach container.

 

SQL Server  >  SQL Server Integration Services