Post reply

Assign bigger(more than 4000 chars) SQL Statement to SQL Statement variable

  • November 20, 2007 at 7:27 am

    #119484

    I have an SSIS package , where i need to assign a "SQL Query" to an "Execute SQL Task" . This Sql Query changes due to certain conditions . So I assign this Sql Query to a variable .

    Now my question is that, my qyery is having length more than 4000 chars. So I can not assign whole query to variable .

    Then what is the way when SQL Statement is having length more than 4000 chars.

    Is there any other way instead of assigning SQL Statement to variable.

  • November 20, 2007 at 7:33 am

    #752693

    Yes, there is probably a better way than wrapping your entire SQL call into a variable, but you'll need to provide more information about what exactly you are trying to do.

    If you are set on using a variable, try using NVARCHAR(MAX), which will give you more than enough space (2 gigabytes, I think) to store your dynamic SQL.

    hth,

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • November 20, 2007 at 7:58 am

    #752712

    It depends on how your query changes. If it is a small set of 2 or 3 different queries based on some criteria, you can use the SQL Command from File option on your Execute SQL task and change the source using expressions.

    If you simply have parameters in your query, my first suggestion would be to create a stored procedure and use parameters. With some details about what you are doing, I may have some better options.

  • November 20, 2007 at 8:47 am

    #752752

    Thanks for ur reply... As U mentioned use Nvarchar(MAx), But when I Add a variable, there is option for string and Char , then I Press 'F4' and go to Property span of that variable.There I select "EvaluateAsExpression" option AS "True" and then In expression I write my query .

    So in this way i do not get any option for Nvarchar(MAx).

    So, How to provide Nvarchar(MAx) option

  • November 20, 2007 at 12:52 pm

    #752859

    There is no NVARCHAR(MAX) data type for an SSIS variable, that is only for T-SQL variables. See my previous post for options when running large queries in SSIS.

  • November 21, 2007 at 4:26 am

    #753030

    Hi Vinod,

    There is another way: in the Execute SQL Task there's a property called SQLSourceType. By default it's set to "Direct input" but contains the options "Variable" and "File connection". You may want to take a look at "File connection". If you can construct your SQL and write it to a file, you can execute the script in the file.

    Hope this helps,

    Andy

    Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply