how to assign Varchar(max) to string variable by using Execute Sql Task in SSIS DECLARE @Col1List VARCHAR(MAX) SELECT @Col1List = ISNULL(@Col1List,'') + '''' + LTRIM(RTRIM(Col1)) + '''' + ',' FROM MY_COL1_TABLE SELECT LTRIM(RTRIM(LEFT(@Col1List,LEN(@Col1List)-1))) Ref:http://social.msdn.microsoft.com/Forums/en/transactsql/thread/ecd5c16f-2878-40ed-8d1b-91f3cb77b107 how to assign Col1List value to string variable in ssis using Execute Sql Task ilikemicrosoft

I guess you need to use DATA CONVERSION transformation in this case. You need to convert your varchar(max) variable to SSIS data type of DT_STR for the variable before you could use it for further processing. Had it been NVARCHAR for the variable, you would have gone with DT_WSTR like-wise. See if this works out for you. Good Luck! Sumit M.

the result of the execute sql task (string) will be used in the oledb source command So i cant use the conversion .... ilikemicrosoft

you can create a stored procedure from your sql command. inside the stored procedure declare an output parameter, and fill it with the result you want. finally you can call that stored procedure from Execute SQL Task in SSIS and in parameter mappings tab map package's variable to the output parameter with appropriate parameter name and direction. here you can see an example with screenshots: http://blogs.msdn.com/b/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspxhttp://www.rad.pasfu.com

Unless you are on SSIS 2012 assigning Varchar(max) to a string [SSIS] variable may be a bad idea because the strings in SSIS are limited to 8000 chars in length and thus may overflow it. Arthur My Blog

Hi surendiran, Please try to changed the ResultSet option of Execute SQL Task from FullResultSet to SingleRow and changed the data type of the variable from String to Object, you can refer to the simliar thread: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/160803e8-4900-4318-a154-8c6ea5094a2d/ Please feel free to ask if you have any question. Thanks, Eileen TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.