Lee posted on February 5, 2009 16:03
It's like editing code in the old enterprise manager procedure window

 

Use caution when pasting code in an EXECUTE SQL TASK in SSIS; there is a limit to the number of lines that you can enter. I never knew this until I started putting more and more code in it, only to find out the hard way. What I did was paste code, then if I needed to add more, I'd open it, paste into SSMS, edit, and then paste back into the window. Pasting code into these things should be avoided anyway - use the file connection as the SQLSourceType to maintain a stealthy method for TSQL in your packages. This little window should be used for TSQL such as LINQ should be for accessing data. Small amounts and minimally. Looking at Books Online, I see nothing at all that mentions the size of input into this task. I was happily working away one day and noticed that as fast as I would paste the code it would disappear.

Relying on this task for a lot of TSQL script is like using the old enterprise manager window to write stored procedures. If you recall, the Enterprise Manager in SQL Server 7 and 2000 used to allow you to enter (and edit) code directly in the stored procedure window. This essentially did an ALTER PROCEDURE on the stored proc, and was a quick and dirty way, mostly dirty, to edit code. Unfortunately it wreaked havoc on managing source control, and if you happened to use the date on the procedure, you were disappointed because the ALTER didn't change the datetime stamp that the proc was compiled. Only a DROP and CREATE did. In other words, it was a lousy place to create and edit code...and likewise the EXECUTE SQL Task window.

I pasted some text with a line counter in the window, and here's what I got - I tried to put 1000 lines in there by the way:


execsqltask



---------------------

 

BOL doesn't always have the answers, does it?

 

 


Posted in:   Tags:
blog comments powered by Disqus

by Lee Everest, M.S.

Poll

Do you use Azure or cloud in your organization?



Show Results

Ads

Search


Month List

Calendar

«  February 2012  »
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
2728291234
567891011
View posts in large calendar

Tags

Disclaimer
The opinions, code, examples, et.al. expressed herein are my own personal opinions and do not represent my employer's view in any way, shape form, or fashion.  All code for demonstration purposes - no guarantees, either written or implied, are made.

© Copyright 2012 Lee Everest's SQL Server, etc. weblog