I have been having an email back-and-forth with Jon Jaques which began with us discussing various limitations regarding BLOB support within the OLE DB Command and ended with Jon’s blog entry SSIS and the Perils of Embedded SQL where he discusses the drawbacks of the Execute SQL Task – namely that the Execute SQL Task has no understanding of the underlying schema thus any changes will not get recognised by SSIS. He’s quite right, the Execute SQL Task does not expose any metadata and hence SSIS does not validate the code within an Execute SQL Task, it only validates the connection.
This is in direct contrast to the SSIS dataflow which is of course a metadata-hungry beast. Some people don’t like the SSIS dataflow for this very reason, its reliance on metadata can make it inflexible and if you want to read some colourful comments about why this is a bad thing then look no further than the never-ending tirade on forum thread Flat File and uneven number of columns. It seems to me Jon has a different perspective – he WANTS SSIS to use that metadata, he WANTS SSIS to growl at him if the schema changes, he WANTS SSIS to be tightly bound to the external metadata; a refreshing perspective methinks. Jon talks about how the SCD component does do this kind of validation:
it generates an Ole DB Destination, and an Ole DB Command; if the inputs to the command object change, invalidating the underlying embedded sql, a warning/error icon is displayed on the object, and when you double click that object, it immediately comes up and tells you which fields are in error, and offers choices for how to handle the problem
Well actually that behaviour isn’t an artefact of the SCD component at all, its an artefact of the dataflow as a whole. That is just how the dataflow works – it consumes metadata of the external data sources and destinations and validates the transformations that you are applying to that data accordingly – in my opinion that’s a huge benefit of using the dataflow over the Execute SQL Task (or, as Jon terms it, embedded SQL).
Unfortunately for Jon he has come up against a limitation of the dataflow which means he is resigned to using a temporary table and thus an Execute SQL Task as I outline in my blog entry Using temporary tables. He suggests that a destination component that creates a temporary table (or even a none-temporary table) when the dataflow executes would be useful and I wholeheartedly agree. I raised a Connect submission asking for this a long time ago but unfortunately Connect is down right now (as it seems to be increasingly so these days) so I can’t link to it; I’ll try and remember to come back and update this later. In the meantime Jon has asked for the same at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=375804 so click through and, if Connect is up, vote for it and add your comments.
UPDATE: Connect is back up and I've found the link to my old submission. Its called "SELECT INTO from destination adapter" and is at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=275841. Also, Jon has replied in the comments section of this blog entry: http://blogs.conchango.com/jamiethomson/archive/2008/10/17/ssis-and-embedded-sql.aspx#comments