Re: SSIS SQL Task Dynamic File Source

Thanks Charles, it worked very nicely.


Charles Kangai wrote:
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:

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!



.

Relevant Pages

  • Invalid cursor state on second $sth->execute
    ... I have written that a script that opens a cursor on a table in a DB2 database then processes each record in the result set inside a loop. ... In the course of the loop I prepare and execute another SQL statement to get a maximum value from the same table for a given condition. ... I then decided that to speed up the script I would prepare this lookup SQL outside the loop using placeholders, so I can just re-execute the same SQL with the different values plugged in each time. ...
    (perl.beginners)
  • Re: How to resume after an Oracle Error ORA-00054
    ... execute in a loop, like you said. ... > use the Oracle FOR UPDATE NOWAIT in our SQL calls. ... I would like the code to try to execute the SQL again rather ...
    (microsoft.public.dotnet.languages.csharp)
  • Safe multithreading in ASP.Net
    ... Code in global.asx Application_start subroutine, ... Dim emails As New ForumEmail ... Do While ContinueNotify 'boolean value set to True so loop will continue ... the thread still continues to execute. ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: What is the difference between for..if, case statements, do..while
    ... ElseIf Then ... It can not execute both This and That or any ... Loop Until ... How does one decide which programming construct to use. ...
    (microsoft.public.excel.programming)
  • Re: What is the difference between for..if, case statements, do..while
    ... ElseIf Then ... It can not execute both This and That or any ... Loop Until ... Do while an loop until cause you to loop throgh the same st of instructions ...
    (microsoft.public.excel.programming)