Convert SSIS Object variable to String/Varchar(MAX) to pass it as a parameter for another Stored Procedure

0
votes

An Execute SQL Task in SSIS have a sql SELECT query which return a Varchar(MAX) value ,And I am assigning that to a SSIS string variable,with resultset as single row, thrown an error regarding Casting.

So I changed SSIS Variable type to an Object.

Now the problem is I have another Execute SQL Task which uses this SSIS variable as a parameter to an Stores Procedure,but it is of type VARCHAR(MAX). How Can I achieve it?

Basically I am trying to get a SSIS Object Variable to Varchar(MAX) or String variable to pass it into another stored Procedure.

2
ssisssis-2012
too many loopholes. Are you sure you want a object variable and not string ? If so, why don't you use full result set ? What exactly your SELECT query is returning ? Is it giving 1 row or multiple rows ? - Prabhat G
Is the select and the SP in the same database? Just write one piece of T-SQL to do it all - Nick.McDermaid
I am pretty sure with single row that you can assign each column to a variable - KeithL
You need to fix the error that you get when trying to store the result in a string variable. Unfortunately you didn't tell us what the error is so we can't assist you further until you provide more information. - Tab Alleman
I am getting a single row with more than 8000 characters and I don't want the data from SQL to get truncated. So I used Object variable in SSIS (Working perfectly fine if I use string Variable in SSIS and SQL query as VARCHAR(8000),but data got truncated). SQL and the final Stored Proc are in different db. I tried to read the value from the object ans assign it to a string variable using a script Task but I could see only 'System._COMObject' as value. - Shibin G

2 Answers

0
votes

Change your variable type back to string and use:

CAST([YourColumn] AS varchar(8000)) AS [YourVariableResultName]

If it doesn't, post the error you get when you do the casting

0
votes

It's resolved . Changed the execute SQL task Result set to full result set and assign it to an object variable.Then used a Foreach item container and set that object variable as source and assigned the 0 index value into a string variable . Then passed it to the stored procedure.Thank you all for your help.,☺️