Tuesday, August 14, 2012

Loop over .sql files using SSIS

This is fairly easy and simple, the requirement is to loop over a folder full of .sql files, that has some queries and run them on the server!

This could be a stress testing queries, create multiple object or drop and recreate indexes queries as in my actual requirement.

We'll need a "For EachLoop container "to iterate over the folder, read the files' location into a STRING variable.

Add a "Execute SQL Task" inside the container, setup the SQL server connection, then setup the query to be from a file connection.

create the file connection, pointing to one of the files.
set the delay validation to true, then setup an expression over the Connection String property.

That's it!!

The package can be downloaded at: https://www.box.com/s/928b2f993c819b64dd53
Hope that helps someone...