Very Computer

Hi

I have a DTS task (Task N) which has 4 precedence tasks,
only one of which can ever execute. It seems as if DTS
assumes an 'AND' operator between the tasks, and there is
no 'OR' option,  so Task N cannot ever execute. I have
therefore split the package in half, and added Task N and
all of its successors to the new package, with the plan to
call the second package from  each of the 4 precedence
tasks. I've created an 'Execute SQL Task' and am using the
syntax below:

EXEC master..xp_cmdshell 'dtsrun /S ServerName /E /N
CalledPackageName', NOOUTPUT

but the called package fails to run. I know there are no
errors with either package, as they both run OK if I call
them directly from DTS designer (SQL 7.0). Both packages
are large, so I don't want to Task N and all of its
successors to each of the four outcomes, although I have
added them to one of the outcomes as a test, and this runs
fine.

I hope this all makes sense? Thanks for reading it through!

Any help would be greatly appreciated.

Peter

Quote:>Hi

>I have a DTS task (Task N) which has 4 precedence tasks,
>only one of which can ever execute. It seems as if DTS
>assumes an 'AND' operator between the tasks, and there is
>no 'OR' option,  so Task N cannot ever execute. I have
>therefore split the package in half, and added Task N and
>all of its successors to the new package, with the plan to
>call the second package from  each of the 4 precedence
>tasks. I've created an 'Execute SQL Task' and am using the
>syntax below:

>EXEC master..xp_cmdshell 'dtsrun /S ServerName /E /N
>CalledPackageName', NOOUTPUT

>but the called package fails to run. I know there are no
>errors with either package, as they both run OK if I call
>them directly from DTS designer (SQL 7.0). Both packages
>are large, so I don't want to Task N and all of its
>successors to each of the four outcomes, although I have
>added them to one of the outcomes as a test, and this runs
>fine.

>I hope this all makes sense? Thanks for reading it through!

>Any help would be greatly appreciated.

>Peter

The most obvious reason for the problem is the potential change in
execution location and security context executing the package.

Using xp_cmdshell means the package will execute on the server, under
either the SQL Server service account for a sysadmin, or the SQL Server
Agent proxy account for a non-sysadmin.

DTS is client side so when developing on your workstation, the package
is executed under your security credentials, on your machine.

I assume you are using SQL Server 7, or else you would have used the
Execute Package Task, in which case an alternative is to use an ActiveX
Script Task-

Execute a package from a package
http://www.sqldts.com/default.aspx?6,104,215,0,1

You might also find these articles on workflow interesting-

Introduction to Workflow
http://www.sqldts.com/default.aspx?6,107,287,0,1

Skip the execution of an individual Task
http://www.sqldts.com/default.aspx?6,103,214,7,1

Multiple Paths in Workflow
http://www.sqldts.com/default.aspx?6,103,218,7,1

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Darren

An excellent set of options, thank you.

I've elected to call the second package using an ActiveX script, and it
executes wothout error.

Many thanks for your help!!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!