Loop Table to Execute Packages

Question

Loop Table to Execute Packages

Dave Repton

SSC Eights!

Points: 811
More actions
April 15, 2008 at 2:38 am

#184432

Hi all,
I've had a good old look around and cannot quite find what I am after - and everything I've tried to adapt has not worked...
So - here is the scenario
I have a series of SSIS packages that I want to be able to run in order. I figure (and could be wrong) that the best way to achieve this would be to store the package names in a table - use the For Each Loop Container to read from this table and pass the package name to an Execute Package Task. Thus when I want to add a new package to the sequence, I create it and then add it's name to the table.
I have manage to create this and pass the package name to a Script Task with a HelloWorld type script, but I don't seem to be able to pass the package name to the Execute Package Task.
If any one can advise whether this is possible ?? Or do I need to create a SP / script to fire a DTExec command ??
If you need any more information please let me know.
Any advice / help / alternative suggestions greatly appreciated...
Regards
D ;o)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You can do what you are indicating. Search around for dynamically setting connection properties and you should find an article or two. I'll search google a bit for something later.

As for an alternative, why wouldn't you just create a SQL Agent job and add each package as a step in the job? This would not only run each package in order, but it would give you a bunch of reporting and error handling options that you would then not have to create yourself. Best of all, there is a GUI for setting then whole thing up.

Dave Repton SSC Eights! Points: 811 More actions · Answer 2

Hmm...don't think it is the Connection Property that needs setting - am trying to set the PackageName property of the Execute Package Task by using the experssions to populate the package name read from the table...

Have created a temp work round that uses a script and an SQL statement, that is populated by the experssions method, using xp_cmdShell to fire DTExec...which seems to be working fine at the moment.

I had thought about one job, but it is looking like there will be over a couple of hundred packages and I wanted a way to group them up and felt that one job with that many steps would be a bit cumbersome...

Any one any other ideas ???

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

I get it - you are using packages stored in the MSDB database, not the file system.

You should be able to do this with a loop container and an expression on the execute package task. Perhaps you configured your expression in the wrong scope? You would need a loop container with the execute package task in it. The expression would need to be configured from within the execute package task.

Even setting up groupings of some sort, I would tend to try to avoid using the command line from a script task to do this. You may want to consider creating a T-SQL procedure and dynamically generates a SQL Agent job to run the entire group of packages. It would be fairly simple to script and you would get the advantages of your packages being managed by the job agent. Just the idea of being able to fix a problem in the middle of the process and continue the job at the package that failed seems pretty useful.

Dave Repton SSC Eights! Points: 811 More actions · Answer 4

Thanks for that Michael...prompted me to take another look and find the problem !!!

I had omitted the backslash in the experssion which is why it wasn;t working...

I had simply

@[User::PackageToFire]

when I actually needed

"\\" + @[User::PackageToFire]

:angry:

Thanks for all your help 😉

prarav SSC Rookie Points: 42 More actions · Answer 5

I hope I understand your question..

I use Execute Package Task in Control flow to execute the SSIS package..

Basically I have one PARENT Pkg that calls pkgs using "Execute Pkg Task" one by one....

When I need to add one more pkg I just have to create the PKG and add it at the end in the Parent Pkg...

This way you can run Pkg Parallel also if they are independent to each other...

Please correct me if I am wrong

Thanks

Dave Repton SSC Eights! Points: 811 More actions · Answer 6

That would work, I am just trying to keep everything in one table so I can also store other criteria as to when the package should run - thus allowing one job to fire both monthly, weekly and daily tasks...