THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Looping over SQL scripts and executing them : SSIS video nugget

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

 

Published Thursday, February 25, 2010 12:02 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi

That is very useful. I have not tried SSIS till I saw your example.

I have been using that approach...

http://dimantdatabasesolutions.blogspot.com/2009/01/executing-multiple-script-file-via.html

February 25, 2010 1:42 AM
 

ihafidh said:

Nice little nugget! You can start your own SSIS TV show.

I actually liked the fact that you had to start the SQL service in the middle of the demo. Makes it more real and people can learn from that.

February 25, 2010 1:47 AM
 

Paul Clancy said:

Hi Jamie, your demo is excellent and clear as could be. I've written a couple of articles on similar topics myself, but I found your demo explained things much quicker and more clearly than text.

Can I ask you what software you used? Particularly for the zooming?

Thanks and best regards,

Paul Clancy

February 25, 2010 4:09 AM
 

jamiet said:

Hiya Paul,

Sure, its Camtasia. Version 6 I think. Its a little bit flaky on Win7 but that zooming feature makes it worth it!

Uri/ihafidh,

Thanks you for the comments, much appreciated!

cheers

Jamie

February 25, 2010 4:16 AM
 

Alejandro Mesa said:

Hi Jamie,

I enjoy a lot these nuggets. They are succinct.

Cheers,

AMB

February 25, 2010 8:12 AM
 

jamiet said:

Alejandro,

Thanks man, that's enough for me! I'll be doing more then!

-Jamie

February 25, 2010 5:33 PM
 

SSIS Junkie : Outputting data to an XML file : SSIS video nugget said:

February 27, 2010 11:53 AM
 

TiborKaraszi said:

Good stuff, Jamie. Pls keep 'em coming.

March 2, 2010 1:39 AM
 

ravi said:

Hi Jamie,

Nice, Just my work completed in 5 minutes ... thanks

-- ravi

April 11, 2012 3:01 AM
 

Longinthetooth said:

Can these files use variables from the apckage variablez:

My scripts are more than 4000 characters long, so they cannot be imbedded into the expression. I want to be able to use 2 or 3 variables in each script

September 28, 2012 3:11 PM
 

Longinthetooth said:

sorry about the spelling: meant to write:

Can these files use variables from the other package variables?

September 28, 2012 3:12 PM
 

jamiet said:

Hi Longinthetooth,

I do not believe there is a way to do that.

regards

Jamie

October 1, 2012 4:17 AM
 

Abi said:

I have this issue where i need it to execute the files one after the other eg. 1a.sql, 1b.sql,2a.sql.. But for some reason it executes in some random order. Do you know a work around that?

Thanks in advance!

October 10, 2013 6:08 PM
 

MGoo said:

Hi Jamie, Many thanks.this has proved very helpful

October 14, 2013 7:17 AM
 

Parminder Jit Singh said:

Hi Jamie,

Not only your tutorial is fantastic and well delivered but saved my life too. Thank you for your valuable services. Keep going

May 22, 2014 5:09 PM
 

ayane said:

This might be a stupid question, but what if you only want to execute ONE sql file. Is it okay to use this solution as well or would you recommend me something else?

August 19, 2014 4:24 AM
 

jamiet said:

Hello ayana,

If you only have one .sql file to execute simply point directly to it from your Execute SQL Task.

August 19, 2014 4:33 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement