DTS Parametirized ExecuteSQlTask

Hi,

I have a DTS whit several SQl tasks that executes a stored procedure. The result of this execution is stored in an output parameter as global variable.

The problem is if i manually launch the DTS package, it works with no problems but after i schedule the job, i received an error in the SQl tasks i said before. What can i do to fix it?

The Error is:
-----
Executed as user: SCCCOL1\sqlservices. ...t: DTSStep_DTSActiveScriptTask_4 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_4 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_26 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_26 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4 DTSRun OnError: DTSStep_DTSExecuteSQLTask_4, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task reported failure on execution. Er... Process Exit Code 1. The step failed.When you schedule a dts task as a job, the permissions used for this job varies. Who is the owner of the job, what login is being used for the sql server agent service and what is the step doing when it fails ?|||Originally posted by rnealejr
When you schedule a dts task as a job, the permissions used for this job varies. Who is the owner of the job, what login is being used for the sql server agent service and what is the step doing when it fails ?

Hi,

Thanks for ur answer. The owner of my job is an windows authenticated user how has administrator permissions over the server, but user used to run sqlserver agent is default user: SqlServices. However in the properties, i've configured the connections like windows authentication ( i supuse that it's using my administrative account to run jobs).

In a previous post, i found that i have to doing bigger the login time-out for SQL Agent. I did it and i rebuild the package into one new with a connection with clear specifications to the server. i mean that before i've reference to [local] server and after i changed it to [NAMESERVER] SQL on my network. I scheduled this package and it works.

is it a bug of SQl Server? why Agent SQl works with a form and not with another?

Thanks,
Maritzita

DTS package workflow

A basic question I know, but can somebody tell me how to add On Success,
On Failure and On Completion arrows between tasks? I have done quite a
bit of DTS, but never had to use these before.
Thanks,
Mike
*** Sent via Developersdex http://www.examnotes.net ***Click Task1
CTRL+Click Task2
Right Click
WorkFlow
TaDa...
Suggest having a look here www.sqldts.com
HTH. Ryan
"Mike P" <mike.parr@.gmail.com> wrote in message
news:eV2vA%23MLGHA.3064@.TK2MSFTNGP10.phx.gbl...
>A basic question I know, but can somebody tell me how to add On Success,
> On Failure and On Completion arrows between tasks? I have done quite a
> bit of DTS, but never had to use these before.
>
> Thanks,
> Mike
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

DTS Package Execution Order

I have a package that has 12 data pump tasks all executing in parallel.

It is transferring raw data from an AS400 DW to a MSSQLSvr Staging area.

Each pump task on completion assigns values to a set of global variables, then having done this passes these as parameters to a sproc which inserts them into a table.

This seems to work for 4 or 5 of the pump tasks but, the rest of the rows in the table are all the same because the remaining pump tasks are all executing before the sprocs.

Is there a way to make sure that the entire set of job steps completes, before starting another job set of steps while still keeping them running in parallel.

I had wondered if there was a way to use the PumpComplete phase of each pump step to fire off the sproc, but can't see how you execute the step.

Any ideas would be much appreciated.Darren Green suggested
Why not take a simper approach and only populate your progress list as
tasks start executing. You could drive this quite happily off events.

To determine order of execution you would need to enumerate all steps as
constraints are held by the task they go to, not from.

For each step, enumerate the PrecedenceConstraints collection, to get
the PrecedenceConstraint objects. The StepName is the preceding step, So
if a step as no PrecedenceConstraints it is the start step. Not sure
that this guaranteed to 100 accurate either as in theory you can change
the basis and result to in effect be a "On Preceeding Step Not Run", and
have a circular reference, but I suspect DTS itself may have the same
problem as you in this case, so probably not worth worrying about for
the start step, but perfectly valid elsewhere.

DTS Package

Hi -
I'm wondering if there is a way to have an "Execute Packge Task" run even if
the package that triggers it has multiple tasks running simultaneously? So
I
don't want the next package to run until the current package is finished, bu
t
there is not a definite start and end point to the tasks in the current
package...
If I can explain further - I don't know which task will end up running last,
so I can't use the workflow of "on success", "execute the next package"...
ThanksCreate a package that executes all other packages. In this super-package you
can then design an appropriate work flow.
ML
http://milambda.blogspot.com/

DTS job not executing all tasks

SQL Server 2000
I created DTS package with several step.
When I run manually, all steps work OK.
When I schedule as job, only the first step runs. The next two steps
do not run.
The log simply says step ...xyz.. did not run
The owner of the job is the same as my regular XP login anme which is
also the same name I run it as manually.
Any ideas?
Thanks
jeffWhat is the login used to start the services of SQL agent.
from
Doller|||>What is the login used to start the services of SQL agent.
system account
I have more specifics about the problem.
I scheduled and ran only the package that makes up the first step of
the previously mentioned package.
This first step (package) runs a command file (DOS .bat) that ftp's
to a remote machine and does an "mget filename".
When this single package is executed manually it all works. I look in
the local machine folder and there is the file.
When run as a scheduled job the log shows the ftp mget getting the
file. However, the file does not exist on the local machine when the
job finishes.
As a matter of fact, if I manually execute the scheduled job, it also
shows that is gets file, but file does not exist on local machine.
jeff
On 19 Apr 2006 19:58:53 -0700, "doller" <sufianarif@.gmail.com> wrote:
>What is the login used to start the services of SQL agent.
>from
>Doller

DTS job not executing all tasks

SQL Server 2000
I created DTS package with several step.
When I run manually, all steps work OK.
When I schedule as job, only the first step runs. The next two steps
do not run.
The log simply says step ...xyz.. did not run
The owner of the job is the same as my regular XP login anme which is
also the same name I run it as manually.
Any ideas?
Thanks
jeffWhat is the login used to start the services of SQL agent.
from
Doller|||>What is the login used to start the services of SQL agent.
system account
I have more specifics about the problem.
I scheduled and ran only the package that makes up the first step of
the previously mentioned package.
This first step (package) runs a command file (DOS .bat) that ftp's
to a remote machine and does an "mget filename".
When this single package is executed manually it all works. I look in
the local machine folder and there is the file.
When run as a scheduled job the log shows the ftp mget getting the
file. However, the file does not exist on the local machine when the
job finishes.
As a matter of fact, if I manually execute the scheduled job, it also
shows that is gets file, but file does not exist on local machine.
jeff
On 19 Apr 2006 19:58:53 -0700, "doller" <sufianarif@.gmail.com> wrote:

>What is the login used to start the services of SQL agent.
>from
>Doller