Store SQL server varchar max in SSIS string

• blasto_max

  SSCertifiable

  Points: 5025

  More actions

  January 8, 2014 at 4:51 pm

  #302419

  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 ?

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  January 9, 2014 at 12:03 am

  #1678679

  Because it is a varchar(max), it is probably BLOB data. Try storing it in an object variable, and then convert it to a string in a script task.

  (there are plenty of resources on the net on how to convert BLOB data to a string in .NET)

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP