SQL Server parameters and SSIS variable Lenght Issues

KrishKKO 1 Reputation point

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 Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,962 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 20,176 Reputation points

    Hi @KrishKKO ,

    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.




    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