Store SQL server varchar max in SSIS string [dupli

Question

This question already has an answer here:

• The type of the value being assigned to variable “User::de_sters” differs from the current variable type 2 answers

What i am doing -

Get single row result set from execute sql task (EST) via oledb connection (OLE).

I want to set ssis variable strName = row's 1st column value (a varchar max). strName is a ssis string FOR SURE. I checked.

In EST - result name = 0, variable name = User::strName.

I get the error -

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

How do I fix this ?

Answer 1

Use an ADO.NET connection. OLEDB cannot cope with varchar(max).

Answer 2

The problem is SSIS doesn't understand varchar(max) as a data type .You need to specify the limit . CAST the varchar(max) column to varchar(8000) to solve the problem. Or you can store the VARCHAR max into a SSIS object and then get the value using object.ToString();