Assign String variable in SSIS

Question

I have a execute sql task in SSIS that I try to store a string value

this is the query

Declare @IdList nvarchar(max)
select @IdList = coalesce( @IdList + ''',''', '') + ID from table
Select @IdList = '''' + @IdList 
Select @IdList

The result looks like

'abc','bcd','def','fds'

and I try to store this value into String variable in SSIS

result set: single row
result name: 0 
Variable Name: String_Contact

this is the error I got

[Execute SQL Task] Error: The value type (__ComObject) can only be converted to variables of type Object.
[Execute SQL Task] Error: An error occurred while assigning a value to variable "String_Contact": "The type of the value (DBNull) being assigned to variable "User::String_Contact" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.

".

Anyone know what's the issue?

thanks

Can you try adding a Set @IdList='' after the declaration and see if that works? Also your type is nvarchar(max) which SSIS would intrepret as DT_NTEXT. You can try with an nvarchar(1000). — TMNT2014
Will try that, thanks! but for the data type, the length of the string might exceed 100000 so I have to use nvarchar(max) — user3399111

Vikramsinh Shinde Vikramsinh Shinde · Accepted Answer · 2014-06-24T15:58:58

Try to change the data type of the variable from String to Object.

If you assign varchar(max) to a string [SSIS] variable. there might be chance of overflow error since strings in SSIS are limited to 8000 chars in length.

But you can assign nvarchar(max) to Object.

You can process object by using For Each Loop Container setting Enumerator as "For Each ADO Enumerator" or by Script Task as

 DataTable dt = new DataTable();
OleDbDataAdapter oleDa = new OleDbDataAdapter();
oleDa.Fill(dt, Dts.Variables["User::variable"].Value);

use dt as regular DataTable.

Resources:

How To Read Object Type Variable In Script Task

Using the SSIS Object Variable as a Result Set Enumerator

Execute SQL Task into an object variable - Shred it with a Foreach loop

Mapping Full Resultset with Execute SQL Task to Object Variable

To use variable in SQLStatement property follow this thread.

Assign String variable in SSIS

2 Answers