SQL Server parameters and SSIS variable Lenght Issues

KrishKKO 1 Reputation point
2021-06-27T17:46:24.033+00:00

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.
2,452 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,736 Reputation points
    2021-06-28T06:19:47.3+00:00

    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.

    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

    0 comments