Today i was working with Execute SQL Task in SSIS (SQL Server 2008 SP1) and I ran in a strange error. When I copied a large amount of a Query into editor window and execute it, it returns an error. It seems that the editor cannot store more than 32767 characters. Because my philosophy is creating projects which I can deploy fast on different systems (dev, Test, Prod) without install scripts and therefore I use lot SQL code(DROP/CREATE) in my packages. In this particular case I want to implement and Drop and Create of a Schema (XSD).
Creating a stored procedure or else was more a plan B option.
So I tried different options with the designer and I finally found I simple solution:
1. Use ‘Built query’
2. Insert the code and press ok
3. You’ll receive an error but you can ignore that.
4. When you look in the editor mode you’ll see that the complete query is there (!?)
But deleting the text and adding new characters won’t work.