Greetings.

I have a folder containing a number of .sql files that I would like to
execute using Exec SQL task I'm looking for a way to dynamically set
the Exec SQL Task's FileConnection property using a ForEachLoop
container for the folder. Probably going to bring the path to the
folder in with a XML Configurtion File. Also trying to dynamically
configure the ForEachLoop container's Enumerator Configuration Folder
property.


Thanks!

Your example is very similar to the Books Online SSIS tutorial example. The
only difference is that while the tutorial examples loops through a list of
files for import, you are looping through the files to execute the SQL
scripts in them.

You will need to place your Execute SQL Task inside a Foreach Loop
container. Configure the Foreach Loop container to store the current
iteration file in a variable. The Execute SQL task has a property called
SQLSourceType; you will need to set this to File connection, then create a
connection manager that points to one of your files. You then want to set the
ConnectionString property of the file connection to point to the variable you
set in the Foreach Loop container. Done!

Work through the tutorial - it takes a very short time to go through and you
will see how to perform the steps I outlined above.

Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email alias: charles
email domain: kangai.demon.co.uk


"Marty" wrote:

Thanks Charles, it worked very nicely.


Charles Kangai wrote:

I am trying to do this also:

I get the following error:

"User::VarFileName" specified in the connection was not valid.

When I try to put user::varfilename in the ConnectionString Field.

I set DelayValidation to true.

Sorry; I figured it out.

syntax is @[User::VarFileName]

Use either @[User::VarFileName] or @VarFileName.

Note that the names are case-sensitive.


Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email: charles at kangai.demon.co.uk



"tgr11" wrote: