SSIS Variables Do Not Like Varchar(MAX)

You may get an error when loading a string variable from a varchar(MAX) field via an Execute SQL Task. Here is the complete error I got when loading a variable named SQLStatement:

Error: 0xC001F009 at I_CTHNDL: The type of the value being assigned to variable “User::SQLStatement” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

A solution is to cast the return value of the select statement or, more drastically, modify the field definition in the database.

SELECT     CAST(SQLStatement AS varchar(8000)) AS SQLStatement
FROM         Z_SQLStatements
WHERE     (SQLStatementRef = 'ExtractHandlings')

8000 is the maximum value allowed in the cast. The SSIS variable happily accepts it.

18 Comments »

Comment by chaps
2008-08-08 16:21:52

Your post was really helpful.Thanks

 
Comment by smmcroberts
2008-10-10 12:32:39

Thanks for the tip, but my string really is larger than 8,000 characters, which is why I was using varchar(max). Is there any way to get SSIS to handle this? Is the SSIS Exec SQL Task return String type really limited to 8,000 characters?

Comment by Tom VdP
2009-03-11 20:43:45

Indeed, the SSIS Exec SQL Task is too limited. We ran into this limitation too and worked around it by using a Script Task to execute the SQL. Strings in the Script Tasks are not limited to that silly 8000 characters limit.

Comment by Artem
2015-06-08 16:13:16

Thank you, Tom!

(Comments wont nest below this level)
 
 
 
Comment by Valentin
2009-03-10 05:43:08

I return it as varchar(max) and assign to Object variable.
How to then in VB script task get the string out of this object?
Convertsion of _ComObject to String is not permitted

Help

Comment by Tom VdP
2009-03-11 20:45:43

Valentin, are you sure you need an Object variable ? Anyway, try the .ToString() method, that should do it, I would say.

Comment by Langston
2009-07-08 19:26:10

.ToString() doesn’t work. See my post below for a solution.

(Comments wont nest below this level)
 
 
Comment by Langston
2009-07-08 19:25:30

See my post below for solution.

 
 
Comment by Langston
2009-07-08 19:24:28

@Tom VdP: You have to assign it to an Object variable because the string variable will not accept varchar(max) result sets. I found that the .ToString() method doesn’t work after you save the result set to an SSIS object variable. All that does is return the data type, not the value of the string itself.

@Tom VdP: Rather than use a script task to execute the SQL, which is more extensive to code and less modular, I found a simpler approach.

To All,
Basically, you have to use a ForEach Loop to “shred” the object variable saved from the SQL Task. See here for details on how to do that (credit Jamie Thompson – SSIS guru): http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx

Once you have your varchar(max) results saved to the Object variable, you loop through it, which in some cases will be only once if all you needed was a single string value in the first place. In the loop’s variable mappings, you map the results from the object variable to an SSIS string variable, and VIOLA! The string value from your varchar(max) result set is now saved to an SSIS string variable.

The strange part is that I do not know why the ForEach loop properly stores the string value of the varchar(max) result set to an SSIS string variable, but you cannot do it in a script task. But hey, this works, so I aint complaining. BTW, this workaround is confirmed in SSIS 2008, although I cannot imagine it would not work in 2005.

 
Comment by Peter
2010-02-03 11:52:04

Actually, I am trying to do just that with a 600 char varchar (from a varchar(1000) field) string, and it is failing.

The SQL-string is loading well in the Object Variable, together with some ID fields. When I display the Object contents the string is shown complete and correct. The next step is to loop through the rows in the Object variable. There, each row is read and fed to separate variables. This step is not working, the foreach loop can’t seem to read the long string properly, and when I MsgBox the variable it just shows a blank.

Jamies blog posts are no longer available either (I saved hyperlinks instead of downloading the pages…).

Any ideas ?

 
Comment by Abhi
2011-04-04 07:22:11

I want to print more than 8000 character in my store procedure so i am using varchar(max) instead of varchar(8000). But still it is showing only 8000 characters. Please help me how to get all characters.

 
Comment by Johnny
2011-07-29 11:05:00

SOLUTION:

This is a limitation of OLEDB.
Use ADO.net connection rather than OLEDB connection.

Comment by Gibson
2011-08-23 21:15:44

I just attempted this with an ADO.Net connection instead of OLEDB and the results were no different. It still fails with ADO.Net.

Comment by Artem
2015-06-08 16:11:43

Use Script Task to execute the SQL!

Comment by Tom VdP
2009-03-11 20:43:45
Indeed, the SSIS Exec SQL Task is too limited. We ran into this limitation too and worked around it by using a Script Task to execute the SQL. Strings in the Script Tasks are not limited to that silly 8000 characters limit.

(Comments wont nest below this level)
 
 
 
Comment by Kristof Bogaert
2012-03-30 08:09:04

Thanks Tom!

Even when we don’t work together anymore, you help me out! :D

Grtz!

 
Comment by Juan Pablo
2013-06-14 18:52:50

Thanks a lot! this was very useful !

 
Comment by David Barrows
2013-12-09 18:37:40

Thanks. This was helpful and did solve the problem in my case.

 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post