CODEWITHSUNDEEP
ssisssis-2012
Shibin G
Shibin G

Reputation: 11

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

I have 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, which is throwing 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 a stored 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.

Upvotes: 0

Views: 5573

Answers (2)

Shibin G
Shibin G

Reputation: 11

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.,☺️

Upvotes: 1

iMajek
iMajek

Reputation: 163

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

Upvotes: 1

Related Questions