Earlier today while surfing the SSIS forum on MSDN I noticed a question that asked how one could execute a series of .sql files using SSIS. One suggested approach was to read the contents of the file(s) into a variable using a Script Task and then execute the contents of that variable using an Execute SQL Task. Well, that would work but there is actually a much quicker and easier way and given that at least two people on that thread didn’t know about it I thought it might be worth putting together a little demo.
I’ve produced a video, embedded below, that demonstrates this technique. Quite simply it loops over the .sql files in question and points the Execute SQL Task directly at those files which saves us from having to read the file contents into a variable. The video is 5m31s long: