start an SSIS package from a particular step?

Question

start an SSIS package from a particular step?

Greg J

SSCarpal Tunnel

Points: 4288
More actions
August 8, 2008 at 12:12 pm

#122848

Users want to be able to launch a package from a particular step. Developers (me) don't want them using Mic Visual Studio to do so.
Is there a DTExecUI-esque utility (preferably included with SQL Server) that allows you to pick the starting step?
Wasn't it the case in SQL2000 that when you ran a DTS package by just double clicking it, it would show you a dialog that let you choose the starting step?
I need this exact tool. DTExecUI doesn't seem to offer anything...
Anyone?

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

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

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

No such luck... SSIS will run all tasks which do not have any preceding tasks first. You would have to build your own logic into handle this.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

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

You could do this using a variable.

You can control your package flow through the use of expressions in your flow connectors. Since a package does not have to be entirely linear, you could connect all of the steps together and use a variable (or a few variables) and expressions to control your data flow.

The trick would be to use an empty sequence container as your "starter" step and then an expression to get you to start at the appropriate step. I have attached a picture of what it would look like.

hodgy SSCertifiable Points: 5906 More actions · Answer 3

although, in the picture provided that scenario wouldn't quite work. you would need to specify the connectors as being "OR" constraints, not "AND" constraints.

That way Process 2 will still run, if that is where you want to start, even though Process 1 hasn't run.

Tom

Life: it twists and turns like a twisty turny thing

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

Michael Earl-395764

SSC Guru

Points: 53873

August 11, 2008 at 10:14 am

#856214

Good catch.

Sarang Bobde Mr or Mrs. 500 Points: 534 More actions · Answer 5

There is no way you can start from a particular step.

Though you may try to achieve this by using variable, expressions, script ask.

There is no way you can start from a particular step.

Though you may try to achieve this by using variable, expressions and script ask.

Just to twist in the logic mentioned above try following:

1) Start package from script task and initially disable all successors of the script task. So nothing runs.

2) Through a comma separated string variable pass the names of the successors that you want to run after script task

3) Loop through all successors and enable only those having names in string variable

4) By doing this you will end up in running only those which are enabled (i.e. end enable only one from where you want to start)

Crispin Proctor SSCoach Points: 18161 More actions · Answer 6

No no no no. SSIS is not designed to be modified at run time. That is a bad idea. While it can be done, you should not. As everyone above has said, write your own logic to handle it. (Besides, far simplier than modifying the package just before runtime.)

Or, Come up with a different solution.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Dave Klug SSCertifiable Points: 5811 More actions · Answer 7

What about breaking out the code for the users into its own package. Create another package for the developers which in turn calls the "user" package.