Using Simple Expressions for Execute SQL Tasks

Ever have a simple Execute SQL task that you need to modify just slightly over and over again?  Like "DELETE FROM myTable".   And then you need "DELETE FROM myOtherTable".  And also "DELETE FROM yetAnotherTable".   And so on.  

So you create an Execute SQL task, assign the connection, code the SQL as direct input:  "DELETE FROM myTable".    Then you do it again, create an Execute SQL task, assign the connection, code the SQL:  "DELETE FROM myOtherTable".   Oh, and because you like good documentation, you also set the Name property of the Execute SQL Tasks:  "DELETE myTable", "DELETE myOtherTable", and so on.   

Well, that's a lot of work.  And while, yes, we might be getting paid by the hour to do all this work, it rubs me the wrong way to have to execute four steps when all I want to do is change one thing (the SQL).  Wouldn't it be nice if we could just copy and paste our first Execute SQL task and quickly modify it?   Well, we can, thanks to SSIS expressions. 

If we set up the first Execute SQL task to have the SqlStatementSource property as @[System::TaskName], whatever we name the Execute SQL task will be the SQL that is executed.   (Hey, this is right in line with our documentation goal!)   Now, if we want to add another simple Execute SQL task (operating on the same database, of course), we can simply copy and paste the Execute SQL Task, and press F2 to rename the task to the SQL we want.  No opening up the Execute SQL dialog -- quick and dirty, four steps reduced to two (ok, I cheat a bit by counting the copy and paste operation as one step). 

I find I do this often, not just because it is convenient, but also because if the SQL is brief, I like to see it displayed on the Execute SQL task in BIDS.  The way that I distinguish Execute SQL tasks which use the @System::TaskName expression (or any other expression) from those which do not is to put  " -- expression" on the end of the SQL which forms the name of the task.  That's a valid ANSI SQL comment which won't interfere with my SQL, but lets me know I need to pull up the expression editor if I really want to know what's going on, should there be a problem.