question

KumarKommalapudiKiran623-8813 avatar image
0 Votes"
KumarKommalapudiKiran623-8813 asked ZoeHui-MSFT answered

SQL Server parameters and SSIS variable Lenght Issues

Hello everyone,
Please some one help me in the below challenge with SQL and SSIS variable length issues.

Issue Description :
I am trying to access the Select query from SSIS with Variable as a source and this variable value will be coming out of a stored procedure.

Step 1 : call the SP from execute SQL task , store the SP outPut into SSIS variable. So issue here is Declaring NVARCHAR(MAX) as Output variable is not accepting in SSIS.

DECLARE @query NVARCHAR(MAX) --> issue here
EXEC SP_Name ?,?, @query OUTPUT

Select @query

If i declare this as NVARCHAR(4000), Full outPut variable out of SP is not sufficient and i am getting 80% of the select query which is wrong.

Is this the limitation here as we can't declare more than 4000 length stings in SSIS when we are calling the SP from execute SQL task? In this case is there any alternatives for this ?

I know if we copy the full query in the Data Flow Task, it is working. but this is not the scenario for me here.

Many thanks in advance for any suggestions .

sql-server-transact-sqlsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @KumarKommalapudiKiran623-8813,

Expressions cannot work with a string that is longer than 4000 characters,

The issue has been discussed below, you may have a try to see if it will be helpful.

https://social.msdn.microsoft.com/Forums/en-US/2ed168fb-e347-4534-a6ed-66061237bc14/sql-query-built-from-task-output-parameter-failing-if-more-than-4000-characters-want-to-use-up-to?forum=sqlintegrationservices

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.