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,468 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 33,701 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 No comments