The Execute SQL task in SSIS is massively more powerful and flexible that its DTS predecessor. You can see a full rundown on a lot of its new capabilities here

The Execute SQL task

One of the things we did leave off that article is outputting a resultset to XML. If you have used the task to output your results to XML and possibly reused them using the XML Task or the XML Source Adapter then why not edit this wiki and tell us about it? I am sure we would all find it informative.

Parameters vs Expressions

When you need to pass values into the ExecuteSQLTask you have two choices, you can use the built in parameter support, or you can use a property expression on the SQLCommand property. They both have benefits so to help you decide here is a quick list-

Expressions

• More flexible in that they can parameterise anything, this is just string concatenation. No limitations on what and where the replacement goes, so can parameterise table names.

Parameters

• Not a SQL injection risk as with string concatenation of expressions.
• Not limited to overall length of 4000 characters, the maximum length of an expression, which will include data values.

Parameters need to be typed. OLE-DB Parameter type from a variable of type DateTime going to a column of type datetime should use the DATE type, not DB_TIMESTAMP as you are probably used to from the SSIS column types.