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

Question

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

vinod.km

SSC Eights!

Points: 971
More actions
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.

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

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

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

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.

vinod.km SSC Eights! Points: 971 More actions · Answer 3

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

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.

Andy Leonard SSChampion Points: 10144 More actions · Answer 5

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