Looping over SQL scripts and executing them : SSIS video nugget

jamiet — Wed, 24 Feb 2010 23:02:00 GMT

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:

If the video is not showing up for whatever reason then you can go and view it over at http://vimeo.com/9714659.

That’s it, hope it was useful! Over on my old blog I used to produce a lot of these how-to blog posts and I termed them SSIS Nuggets – I liked the connotation of them being bite-sized bits of info that you could easily consume. I’d like to get back into producing SSIS nuggets and I reckon that videos like this could be a useful way of communicating them. Does that sound like a good idea? Let me know in the comments!

@Jamiet

SSIS Junkie : video, For Each Loop, ssis

Looping over SQL scripts and executing them : SSIS video nugget