Assigning value from single row result set in ssis

Question

I am trying to get CSV IDs from a table from sql server and assign the result to a variable. below is the sql I have put inside the Execute SQL Task

set nocount on
declare @csv varchar(max) = ''
select @csv = @csv + cast(companyid as varchar(10)) + ',' from company where isprocessed = 0
select substring(@csv,1,len(@csv) - 1) as companyids

As you can see its a simple and standard way of getting csv of a field in t-sql. It works perfectly in query window but throwing below error when I run the Task in SSIS 2012

[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 "sCSVCompanyIds": "The type of the value (DBNull) being assigned to variable "User::sCSVCompanyIds" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

Below are the settings of Execute SQL Task

1. In General tab, Resultset project is set to single row
2. In Result Set tab, Result Name is set to 0 (I also tried by setting it to csvids which is the alias column name in the select list) and Variable Name is set to User::sCSVCompanyIds

I have no clue why its not working. After wasting so much time I am worked out a hard way which by returning the result as Full Row set (same SQL which returns 1 row 1 column always) and add a for each loop container to loop throw the result set (which will always iterates only once for obvious reasons) and assign the result set's fields to the variable. It works for me but there should be easy way of doing it. What I am missing?

Answer 1

The problem is with the nvarchar(max) data type. I assume it will be same for varchar(max) also. Though relevant data types in SSIS are DT_NTEXT and DT_TEXT are present for some reason we are getting this error.

There are multiple options to handle this.

One Of course there might be more appropriate way to handle this by cast/converting the column within the query to fixed length instead of max something like cast(myvarcharmaxfield as varchar(8000)). In my case it doesn't work because I expect bigger length string. I am generating a csv string from a unique identifier column which itself is 36 chars long string and needs 3 extra chars for quoting them with signle quote and a comma as separator which will support only 205 values. So it doesn't work for me.

So I left with no option but to stick the way I implemented already which is in my question

After wasting so much time I am worked out a hard way which by returning the result as Full Row set (same SQL which returns 1 row 1 column always) and add a for each loop container to loop throw the result set (which will always iterates only once for obvious reasons) and assign the result set's fields to the variable

The new way I learnt is from an unaccepted answer from this question

Change the Oledb connection to ADO.NET connection

I think this is fair enough but it requires me to create another connection manager (so that I don't have to change all existing tasks) and use it for this type of taks where I need csv but I did not buy it as I have very little time to doing research on creating connection string of it which appears to me with the type of erros I am getting is different from oledb.