how to run all the SSIS packages in a folder using Execute Package Task

Hi to all,

I have created for each container to call all the packages in a folder like below, also created a variable.

then i add execute package task inside of foreach container and selected file system in a location

and in connection called currently creating package name 

finally in connection properties i added variable in expression which i created and mapped into for each loop container

i referred below link 

https://www.simple-talk.com/sql/ssis/executing-ssis-packages-/

all the packages are running but its not ending once all the packages executed its re run and continue the running process, how to stop once all the packages execute. 

thanks in advance.

June 26th, 2015 12:47am

Looks like the ForEach Loop is faulting.

But it is not possible for the file enumerator to continue past the number of qualified files.

I suggest you debug.

Put breakpoints and observe the variable values to determine what makes the package execute indefinitely.

Free Windows Admin Tool Kit Click here and download it now
June 26th, 2015 9:53am

Hi ,

i put breakpoints and checked because of foreach loop its running indefinitely.

how to run all packages without using Foreach loop.

June 27th, 2015 1:39am

Does the loop ever hit the breakpoint so the execution suspends?
Free Windows Admin Tool Kit Click here and download it now
June 27th, 2015 6:29pm

June 29th, 2015 12:21am

Hmm... Sounds fishy

Is the for each loop itself nested? Is this package called from another master package?

Free Windows Admin Tool Kit Click here and download it now
June 29th, 2015 2:38am

hi visakh,

not this is single package its not called from any package.

for more information, kindly refer the below mentioned link

https://www.simple-talk.com/sql/ssis/executing-ssis-packages-/

June 29th, 2015 2:42am

you put two breakpoints whereas I asked for one.

The upper loop is suspended though, right?

So what are the variable values?

Free Windows Admin Tool Kit Click here and download it now
June 29th, 2015 9:30pm

ya for each loop in process.

the variable are

name  => package to run

scope  => package1

data type => string

value    =>

then i called this variable in for each variable mappings and set index value is 0

in package execute task location is file system then created connection for current package

June 30th, 2015 12:10am

where scope?

I do not think you followed the blog post you mentioned.

Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 10:50pm

hi arthurz,

is there any other method to run all the packages in a folder , if yes can you please share.

July 1st, 2015 12:07am

If I were you, I would elect to running them via DTEXEC.exe as a bat file e.g.

dtexec /f "c:\pkgOne.dtsx"
dtexec /f "c:\pkgTwo.dtsx"
dtexec /f "c:\pkgThree.dtsx"

simple and elegant IMO

Free Windows Admin Tool Kit Click here and download it now
July 1st, 2015 8:50pm

While I would expect it to fail, are you possibly trying to execute the main package (within the loop)? Therefore entering an infinite loop...
  • Edited by RyanAB 5 hours 8 minutes ago
July 1st, 2015 9:59pm

hi,

i already tried DTEXEC.exe its so nice method, thank you so much for replying me,

in this DTEXEC have to specify package name. instead of each package i want to execute all the packages in a folder

example DTEXEC /f "C:/*.dtsx"  this is not working.

Free Windows Admin Tool Kit Click here and download it now
July 1st, 2015 11:56pm

hi RyanAB,

yes the mainpackage is called in Loop.

July 1st, 2015 11:58pm

hi RyanAB,

yes the mainpackage is called in Loop.

Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2015 12:32am

hi,

i dont know how to prevent can you explain briefly with screen shot it would be more helpful.

July 2nd, 2015 12:49am

While I would expect it to fail, are you possibly trying to execute the main package (within the loop)? Therefore entering an infinite loop...
  • Edited by RyanAB Thursday, July 02, 2015 1:57 AM
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2015 1:57am

While I would expect it to fail, are you possibly trying to execute the main package (within the loop)? Therefore entering an infinite loop...
  • Edited by RyanAB Thursday, July 02, 2015 1:57 AM
July 2nd, 2015 1:57am

While I would expect it to fail, are you possibly trying to execute the main package (within the loop)? Therefore entering an infinite loop...
  • Edited by RyanAB Thursday, July 02, 2015 1:57 AM
Free Windows Admin Tool Kit Click here and download it now
July 2nd, 2015 1:57am

What I'd do, put in an expression task and disable it (it doesn't need to do anything but you need the link that follows)

Connect the expression task to the Execute Package Task

Double click the link to open the Precedence Constraints and add the expression @[User::PackageToRun] != @[System:PackageName]

(might have to be some fiddling there to get them matching)

July 2nd, 2015 3:50pm

ryan,

we are using SQL server 2008 R2 , expression task is not available .

Free Windows Admin Tool Kit Click here and download it now
July 3rd, 2015 12:07am

ryan,

we are using SQL server 2008 R2 , expression task is not avail

July 3rd, 2015 10:50pm

hi,

i already tried DTEXEC.exe its so nice method, thank you so much for replying me,

in this DTEXEC have to specify package name. instead of each package i want to execute all the packages in a folder

example DTEXEC /f "C:/*.dtsx"  this is not work

Free Windows Admin Tool Kit Click here and download it now
July 3rd, 2015 10:55pm

hi arthurz,

its working in the below bat file,

for %1 in (D:\deletepack\deletepack\*.dtsx) do dtexec /F "%1"

in this code its showing error in last %%f

FOR %%f IN (C:\PackagesToExecute\*.dtsx) DO DTEXEC /f %%f

-----------------------------------

already i placed main package in the directory.

also i am having intermediate knowledge of ssis i dont know to write VB or C# code in inside of script task. so in script task can you please explain more.

July 4th, 2015 1:42am

Not sure what error you get
Free Windows Admin Tool Kit Click here and download it now
July 4th, 2015 7:05pm

hi arthur,

i dont know how to use script task for this scenario.

July 5th, 2015 11:47pm

Sorry, I need to understand what specifically you cannot accomplish with it
Free Windows Admin Tool Kit Click here and download it now
July 6th, 2015 8:26pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics