CODEWITHSUNDEEP
sql-serverssis
James Khan
James Khan

Reputation: 841

SSIS Data Types

I have an execute sql task ( Task 1 ) which runs sql to returns a column called Note from Table A and stores it as a String SSIS variable type. In Table A, Note is defined as varchar(2000).

I then have an execute sql task ( Task 2) to run a stored procedure. The input parameter is Note varchar(max).

I run these 2 task in SSIS and get the following error:

DECLARE @..." failed with the following error: "The text, ntext, and image data types are invalid for local variables.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have tried several solutions with no success. How can you get round this error and get SSIS to store the variable and feed it to the SP ?

On sql server 2012 SSIS and hitting an old 2008 database where the SP resides.

Upvotes: 0

Views: 599

Answers (2)

benjamin moskovits
benjamin moskovits

Reputation: 5458

You are somehow mapping a text type column in your tsql code. Before you return from your tsql proc convert the text column to a varchar column with select cast (textval as varchar(max)) and make sure your output variables are defined as varchars.

Upvotes: 1

Dance-Henry
Dance-Henry

Reputation: 953

You need to make sure the Task 1 should only return one row. example capture

Otherwise you need to use an Object variable. And use a For each loop container to loop thru that object.

Upvotes: 0

Related Questions