DTS shedule error

Hey,
I have a DTS and I can execute him with no error.
This DTS is sheduled every day. But when the DTS is started up from the
shedule, he always fails.
He gives follwing error and I don't understand what is wrong.
Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
OnError: DTSStep_DTSActiveScriptTask_1, 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: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Help
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Tkx,
Jac
When you execute the package manually it is runs with your security
privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessing
that your system does not have privileges to do what you want.
"Jac" wrote:

> Hey,
> I have a DTS and I can execute him with no error.
> This DTS is sheduled every day. But when the DTS is started up from the
> shedule, he always fails.
> He gives follwing error and I don't understand what is wrong.
> Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
> OnError: DTSStep_DTSActiveScriptTask_1, 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: 1100 Error Detail Records: Error:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
> OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Tkx,
> Jac
|||Hey,
I am not very well in security. So, how can I give the user OGEWS140\SYSTEM
all the security rights so he can execute a sheduled DTS
tkx,
Jac
"cathedr@.wa.state.gov" wrote:
[vbcol=seagreen]
> When you execute the package manually it is runs with your security
> privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessing
> that your system does not have privileges to do what you want.
> "Jac" wrote:
|||In Enterprise Manager, expand the server you are working on. Then expand the
Management Folder. Right-click the SQL Server Agent and choose Properties
from the pop-up menu. My guess is that you will find the Service startup
account set to System account. When you schedule a DTS package, the SQL
Server Agent is what starts the package. Since the Agent is starting the
package, that is the security context that the package runs in. If you need
the package to run in a different security context, you could specify that
the Agent use a different account. You could create a local account on the
server specifically for this purpose. If any of your jobs or packages will
need to perform administrator tasks, you should give the account appropriate
privileges on both the server and SQL server. The account will also need
privileges to any databases, procedures, etc. If your jobs or packages need
to communicate with other servers, you may need to use a domain account
rather than a local account. Another reason you may want to use a domain
account is in order to use SQL Mail. The startup account that I use for
Agent is a domain account that has an email account on Exchange.
Hope that helps...
Dan
"jac" wrote:
[vbcol=seagreen]
> Hey,
> I am not very well in security. So, how can I give the user OGEWS140\SYSTEM
> all the security rights so he can execute a sheduled DTS
> tkx,
> Jac
> "cathedr@.wa.state.gov" wrote:
|||Thanks a lot. That was a great help to me.
"cathedr@.wa.state.gov" wrote:
[vbcol=seagreen]
> In Enterprise Manager, expand the server you are working on. Then expand the
> Management Folder. Right-click the SQL Server Agent and choose Properties
> from the pop-up menu. My guess is that you will find the Service startup
> account set to System account. When you schedule a DTS package, the SQL
> Server Agent is what starts the package. Since the Agent is starting the
> package, that is the security context that the package runs in. If you need
> the package to run in a different security context, you could specify that
> the Agent use a different account. You could create a local account on the
> server specifically for this purpose. If any of your jobs or packages will
> need to perform administrator tasks, you should give the account appropriate
> privileges on both the server and SQL server. The account will also need
> privileges to any databases, procedures, etc. If your jobs or packages need
> to communicate with other servers, you may need to use a domain account
> rather than a local account. Another reason you may want to use a domain
> account is in order to use SQL Mail. The startup account that I use for
> Agent is a domain account that has an email account on Exchange.
> Hope that helps...
> Dan
> "jac" wrote:

DTS shedule error

Hey,
I have a DTS and I can execute him with no error.
This DTS is sheduled every day. But when the DTS is started up from the
shedule, he always fails.
He gives follwing error and I don't understand what is wrong.
Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
OnError: DTSStep_DTSActiveScriptTask_1, 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: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Help
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Tkx,
JacWhen you execute the package manually it is runs with your security
privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessing
that your system does not have privileges to do what you want.
"Jac" wrote:
> Hey,
> I have a DTS and I can execute him with no error.
> This DTS is sheduled every day. But when the DTS is started up from the
> shedule, he always fails.
> He gives follwing error and I don't understand what is wrong.
> Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
> OnError: DTSStep_DTSActiveScriptTask_1, 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: 1100 Error Detail Records: Error:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
> OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Tkx,
> Jac|||Hey,
I am not very well in security. So, how can I give the user OGEWS140\SYSTEM
all the security rights so he can execute a sheduled DTS
tkx,
Jac
"cathedr@.wa.state.gov" wrote:
> When you execute the package manually it is runs with your security
> privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessing
> that your system does not have privileges to do what you want.
> "Jac" wrote:
> > Hey,
> >
> > I have a DTS and I can execute him with no error.
> > This DTS is sheduled every day. But when the DTS is started up from the
> > shedule, he always fails.
> > He gives follwing error and I don't understand what is wrong.
> >
> > Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
> > Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
> > OnError: DTSStep_DTSActiveScriptTask_1, 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: 1100 Error Detail Records: Error:
> > -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> > reported failure on execution. Error source: Microsoft Data
> > Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> > context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
> > OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> > DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
> > Exit Code 1. The step failed.
> >
> > Tkx,
> > Jac|||In Enterprise Manager, expand the server you are working on. Then expand the
Management Folder. Right-click the SQL Server Agent and choose Properties
from the pop-up menu. My guess is that you will find the Service startup
account set to System account. When you schedule a DTS package, the SQL
Server Agent is what starts the package. Since the Agent is starting the
package, that is the security context that the package runs in. If you need
the package to run in a different security context, you could specify that
the Agent use a different account. You could create a local account on the
server specifically for this purpose. If any of your jobs or packages will
need to perform administrator tasks, you should give the account appropriate
privileges on both the server and SQL server. The account will also need
privileges to any databases, procedures, etc. If your jobs or packages need
to communicate with other servers, you may need to use a domain account
rather than a local account. Another reason you may want to use a domain
account is in order to use SQL Mail. The startup account that I use for
Agent is a domain account that has an email account on Exchange.
Hope that helps...
Dan
"jac" wrote:
> Hey,
> I am not very well in security. So, how can I give the user OGEWS140\SYSTEM
> all the security rights so he can execute a sheduled DTS
> tkx,
> Jac
> "cathedr@.wa.state.gov" wrote:
> > When you execute the package manually it is runs with your security
> > privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessing
> > that your system does not have privileges to do what you want.
> >
> > "Jac" wrote:
> >
> > > Hey,
> > >
> > > I have a DTS and I can execute him with no error.
> > > This DTS is sheduled every day. But when the DTS is started up from the
> > > shedule, he always fails.
> > > He gives follwing error and I don't understand what is wrong.
> > >
> > > Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
> > > Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
> > > OnError: DTSStep_DTSActiveScriptTask_1, 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: 1100 Error Detail Records: Error:
> > > -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> > > reported failure on execution. Error source: Microsoft Data
> > > Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> > > context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
> > > OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> > > DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
> > > Exit Code 1. The step failed.
> > >
> > > Tkx,
> > > Jac|||Thanks a lot. That was a great help to me.
"cathedr@.wa.state.gov" wrote:
> In Enterprise Manager, expand the server you are working on. Then expand the
> Management Folder. Right-click the SQL Server Agent and choose Properties
> from the pop-up menu. My guess is that you will find the Service startup
> account set to System account. When you schedule a DTS package, the SQL
> Server Agent is what starts the package. Since the Agent is starting the
> package, that is the security context that the package runs in. If you need
> the package to run in a different security context, you could specify that
> the Agent use a different account. You could create a local account on the
> server specifically for this purpose. If any of your jobs or packages will
> need to perform administrator tasks, you should give the account appropriate
> privileges on both the server and SQL server. The account will also need
> privileges to any databases, procedures, etc. If your jobs or packages need
> to communicate with other servers, you may need to use a domain account
> rather than a local account. Another reason you may want to use a domain
> account is in order to use SQL Mail. The startup account that I use for
> Agent is a domain account that has an email account on Exchange.
> Hope that helps...
> Dan
> "jac" wrote:
> > Hey,
> >
> > I am not very well in security. So, how can I give the user OGEWS140\SYSTEM
> > all the security rights so he can execute a sheduled DTS
> >
> > tkx,
> > Jac
> >
> > "cathedr@.wa.state.gov" wrote:
> >
> > > When you execute the package manually it is runs with your security
> > > privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessing
> > > that your system does not have privileges to do what you want.
> > >
> > > "Jac" wrote:
> > >
> > > > Hey,
> > > >
> > > > I have a DTS and I can execute him with no error.
> > > > This DTS is sheduled every day. But when the DTS is started up from the
> > > > shedule, he always fails.
> > > > He gives follwing error and I don't understand what is wrong.
> > > >
> > > > Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
> > > > Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
> > > > OnError: DTSStep_DTSActiveScriptTask_1, 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: 1100 Error Detail Records: Error:
> > > > -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> > > > reported failure on execution. Error source: Microsoft Data
> > > > Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> > > > context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
> > > > OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> > > > DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
> > > > Exit Code 1. The step failed.
> > > >
> > > > Tkx,
> > > > Jac

DTS shedule error

Hey,
I have a DTS and I can execute him with no error.
This DTS is sheduled every day. But when the DTS is started up from the
shedule, he always fails.
He gives follwing error and I don't understand what is wrong.
Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
OnError: DTSStep_DTSActiveScriptTask_1, 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: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Hel
p
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Tkx,
JacWhen you execute the package manually it is runs with your security
privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessin
g
that your system does not have privileges to do what you want.
"Jac" wrote:

> Hey,
> I have a DTS and I can execute him with no error.
> This DTS is sheduled every day. But when the DTS is started up from the
> shedule, he always fails.
> He gives follwing error and I don't understand what is wrong.
> Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
> OnError: DTSStep_DTSActiveScriptTask_1, 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: 1100 Error Detail Records: Erro
r:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The tas
k
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp H
elp
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSR
un
> OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Proces
s
> Exit Code 1. The step failed.
> Tkx,
> Jac|||Hey,
I am not very well in security. So, how can I give the user OGEWS140\SYSTEM
all the security rights so he can execute a sheduled DTS
tkx,
Jac
"cathedr@.wa.state.gov" wrote:
[vbcol=seagreen]
> When you execute the package manually it is runs with your security
> privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guess
ing
> that your system does not have privileges to do what you want.
> "Jac" wrote:
>|||In Enterprise Manager, expand the server you are working on. Then expand th
e
Management Folder. Right-click the SQL Server Agent and choose Properties
from the pop-up menu. My guess is that you will find the Service startup
account set to System account. When you schedule a DTS package, the SQL
Server Agent is what starts the package. Since the Agent is starting the
package, that is the security context that the package runs in. If you need
the package to run in a different security context, you could specify that
the Agent use a different account. You could create a local account on the
server specifically for this purpose. If any of your jobs or packages will
need to perform administrator tasks, you should give the account appropriate
privileges on both the server and SQL server. The account will also need
privileges to any databases, procedures, etc. If your jobs or packages need
to communicate with other servers, you may need to use a domain account
rather than a local account. Another reason you may want to use a domain
account is in order to use SQL Mail. The startup account that I use for
Agent is a domain account that has an email account on Exchange.
Hope that helps...
Dan
"jac" wrote:
[vbcol=seagreen]
> Hey,
> I am not very well in security. So, how can I give the user OGEWS140\SYSTE
M
> all the security rights so he can execute a sheduled DTS
> tkx,
> Jac
> "cathedr@.wa.state.gov" wrote:
>|||Thanks a lot. That was a great help to me.
"cathedr@.wa.state.gov" wrote:
[vbcol=seagreen]
> In Enterprise Manager, expand the server you are working on. Then expand
the
> Management Folder. Right-click the SQL Server Agent and choose Properties
> from the pop-up menu. My guess is that you will find the Service startup
> account set to System account. When you schedule a DTS package, the SQL
> Server Agent is what starts the package. Since the Agent is starting the
> package, that is the security context that the package runs in. If you ne
ed
> the package to run in a different security context, you could specify that
> the Agent use a different account. You could create a local account on th
e
> server specifically for this purpose. If any of your jobs or packages wil
l
> need to perform administrator tasks, you should give the account appropria
te
> privileges on both the server and SQL server. The account will also need
> privileges to any databases, procedures, etc. If your jobs or packages ne
ed
> to communicate with other servers, you may need to use a domain account
> rather than a local account. Another reason you may want to use a domain
> account is in order to use SQL Mail. The startup account that I use for
> Agent is a domain account that has an email account on Exchange.
> Hope that helps...
> Dan
> "jac" wrote:
>sqlsql

DTS Scheduled job always fails

Hi,

I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!Did you ever find a solution to this problem? I am having the same problem.

Originally posted by multiplex77
Hi,

I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!|||You execute under different security contexts when executing a dts package manually versus scheduling it as a job. What is your dts script doing ?|||I set up a number of packages to process selected cubes in Analysis Services. The first job processes all dimensions and even that one fails.|||What message is returned for the failure ?|||ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||True, the problem is exactly as described by multiplex77. the only jobs that fail are the ones that are processing OLAP cubes/dimensions

Originally posted by rnealejr
ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||Who is the owner of the job ?|||The owner is sa and the password was specified when the DTS packages were saved. SLQ Server Agent is logged on as administrator.

Originally posted by rnealejr
Who is the owner of the job ?|||log the package and post the message(s) returned in the log.|||I don't see a way to log the package. I've never had to do that.
Originally posted by rnealejr
log the package and post the message(s) returned in the log.|||If the SQL Agent is running as the local administrator, is the local administrator a member of the local Olap Administrators group? Not sure if it defaults to this.|||The SQlSERVERAGENT does not log on as the local administrator. It logs on as a system wide administrator. We have been looking for the OLAP administrator group in the 'security' tab and the 'member of' tab for the administrator, but don't find it listed in any of the drop down lists. We have all the latest service packs installed.|||The Olap Administrators group is a local group created by the Analysis Services install. Anyone who is not a member of this group can not use the SQL Server tools to manage, modify, or otherwise peek at the cubes on that machine. Remember this only applies to the MMC tool. The OLAP Administrators group will not show up in the domain-wide dropdown lists, since it is local only to one machine. Does that help?|||Right click your "My Computer" -> Manage -> Local Users and Groups -> Groups -> Olap Admin ... Add the account that is used to run sql server agent.|||UPDATE: We discovered that the OLAP Administrator group had somehow been deleted from the Active directory. We tried re-installing service pack 3 for Analysis Services, recreating the OLAP Administrators group and setting the members, properties etc. None of this worked. We just re-installed Analysis Services and service pack 3 on the server and now the jobs will run from the workstation in EM|||The OLAP administrators group is not in AD. Never was. It is local to the Analysis Services machine.|||Our Analysis Services server is a Domain controller, and as such cannot use the Local Users.. snap in. You get directed to the Active Directory Users and Computers when you try opening it. (also ref. MS KB article 829738 'More Information')|||Once you installed it as a domain controller in ad, the local users/groups do not transfer - which is why the olap admin group never appeared. I am a little curious that other problems did not appear before this problem.

Thanks for posting the follow-up with the solution.

DTS Scheduled Job

I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.DTS is a client tool and thus when you run it from EM it's running
on your pc. If the server doesn't have an S drive then you'll get this
error. It's best to use UNC paths to avoid ambiguity. Also check out
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q269074
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"L Dominguez" <ldominguez@.cardionet.com> wrote in message
news:02af01c34730$9a2823b0$a601280a@.phx.gbl...
I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.

DTS return

I am trying to execute a DTS package which will update four tables on a central server from various souces (each source will insert records into all four tables).

We are running this code by calling a stored procedure from VB, which includes code to execute the DTS package. The problem I am having is the VB code continues on even though the package has completed.

SET @.SQLStr = 'DTSRun /S CENTRAL1 /N DTS_TEST_HU_MoveCenterScreens /E'
EXEC @.Result = Master.dbo.xp_cmdshell @.SQLStr

I return @.Result. I would have thought that the return value from the execution of the sql package would not return until it was completed. However, it returns right away. The package takes about 10 mintutes to run, but the return variable is populated in less than a second.

The next step of the process relies upon the dts's completion.
I am using SQL 2000 as my DB

Any thoughts?You should take a look at this article:

Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)

Have you thought about executing DTS from VB?|||Thank you for the response. While I was still trying to figure out what to do I started to explore VB and found the DTS object. We are now using that object and it has solved my problems.
As for the link, I was trying to utilize the return variable but my code continued to execute even if the dts package was not complete.

Originally posted by achorozy
You should take a look at this article:

Execute DTS via Stored Procedure (http://www.databasejournal.com/features/mssql/article.php/1459181)

Have you thought about executing DTS from VB?sqlsql

DTS question for Microsoft

Simple question, hope there's a simple answer. ok, when
you have a DTS "Execute SQL Task" step, is there any way
AT ALL, that I can see the output from that SQL? I am
NOT looking to do anything more then debug VERY large
poorly written DTS packages that have MANY steps, and
seeing the normal SQL output from the steps would help a
LOT. Also, if a step has a friendly name like "Cleanse
Customer Table" why can't THAT be shown in the output,
instead of a name like "DTSStep_DTSExecuteSQLTask_1".
Those two points make large DTS packages a nightmare to
maintain. So, I'm hoping someone knows a trick to see
REAL SQL output and not just a list like I DO see
below... When an error happens we must script out the DTS
package to VBscript and find the code that way, which is
not an easy method to debug at 3am... Thanks, Bruce
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_44
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_44
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_6
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_12
etc, etc, etc...Bruce,
if you open up the package and right-click on the white background then open
up disconnected edit, you can browse to the step, select the step name eg
DTSStep_DTSDynamicPropertiesTask_1 and rename it to anything you want. There
are some dependencies when remaning things here - if you rename a task, the
precedence constraint refers to the old name and will itself need renaming
also.
HTH,
Paul Ibison

DTS question for Microsoft

Simple question, hope there's a simple answer. ok, when
you have a DTS "Execute SQL Task" step, is there any way
AT ALL, that I can see the output from that SQL? I am
NOT looking to do anything more then debug VERY large
poorly written DTS packages that have MANY steps, and
seeing the normal SQL output from the steps would help a
LOT. Also, if a step has a friendly name like "Cleanse
Customer Table" why can't THAT be shown in the output,
instead of a name like "DTSStep_DTSExecuteSQLTask_1".
Those two points make large DTS packages a nightmare to
maintain. So, I'm hoping someone knows a trick to see
REAL SQL output and not just a list like I DO see
below... When an error happens we must script out the DTS
package to VBscript and find the code that way, which is
not an easy method to debug at 3am... Thanks, Bruce
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_44
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_44
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_6
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_12
etc, etc, etc...
Bruce,
if you open up the package and right-click on the white background then open
up disconnected edit, you can browse to the step, select the step name eg
DTSStep_DTSDynamicPropertiesTask_1 and rename it to anything you want. There
are some dependencies when remaning things here - if you rename a task, the
precedence constraint refers to the old name and will itself need renaming
also.
HTH,
Paul Ibison
sqlsql

DTS question for Microsoft

Simple question, hope there's a simple answer. ok, when
you have a DTS "Execute SQL Task" step, is there any way
AT ALL, that I can see the output from that SQL? I am
NOT looking to do anything more then debug VERY large
poorly written DTS packages that have MANY steps, and
seeing the normal SQL output from the steps would help a
LOT. Also, if a step has a friendly name like "Cleanse
Customer Table" why can't THAT be shown in the output,
instead of a name like "DTSStep_DTSExecuteSQLTask_1".
Those two points make large DTS packages a nightmare to
maintain. So, I'm hoping someone knows a trick to see
REAL SQL output and not just a list like I DO see
below... When an error happens we must script out the DTS
package to VBscript and find the code that way, which is
not an easy method to debug at 3am... Thanks, Bruce
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_44
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_44
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_6
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_7
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_12
etc, etc, etc...Bruce,
if you open up the package and right-click on the white background then open
up disconnected edit, you can browse to the step, select the step name eg
DTSStep_DTSDynamicPropertiesTask_1 and rename it to anything you want. There
are some dependencies when remaning things here - if you rename a task, the
precedence constraint refers to the old name and will itself need renaming
also.
HTH,
Paul Ibison

DTS question

Is there a way to use Global variables in the 'Execute SQL task' in a DTS package? if no is there a way to?Yes, depends on what version of SQL Server you have. In SQL Server 2000, you can use question marks as place holders for parameters, then assign the global variables to the question marks using the parameters button in the execute sql screen.

If you are using sql7 then I found this to work. Add an active x script to the package that creates and sets the sql command of the execute sql task. when you create the sql command in the vb script, use the variables you want. Here is the syntax of the active x script.

Steve

'*********************************
' Visual Basic ActiveX Script
'*********************************

Function Main()

Dim oPkg, oExecSQL, sSQLStatement

sSQLStatement = "EXEC stpr_createtable " & DTSGlobalVariables("tableName").Value

Set oPkg = DTSGlobalVariables.Parent
Set oExecSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask

oExecSQL.SQLStatement = sSQLStatement

Set oExecSQL = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function

DTS Question

I would use the DTSRUN command to execute the DTS package. On the DTSRUN
command you can use the /L option to create a log froma DTS package.
"shub" wrote:

> I am using SQL Server 2000 and I have a created a DTS package that
> copies Data from a Excel Spreasheet to a SQL Server table then executes
> a user Stored Proc
> What I am wanting to do is if for some reason the Stored Proc errors
> out because of bad data , I would like to see the errors reported in a
> file so that it can be reviewed and I was not sure if there is a way to
> output the errors in DTS. When I run the Stored Proc theough Query
> Analyzer I see the errors in Query Analyzer, I basically want to see
> the same information after running the DTS. Can this be done, if so
> how.
> Any help in this regard is greatly appreciated.
> Thanks
>Thanks Greg for your response. I will definitely look at that option.
Is there any way this option could be incorporated when executed
through enterprise manager.
Greg Larsen wrote:[vbcol=seagreen]
> I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> command you can use the /L option to create a log froma DTS package.
> "shub" wrote:
>|||I don't know of any way, sorry.
"shub" wrote:

> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
>|||I am using SQL Server 2000 and I have a created a DTS package that
copies Data from a Excel Spreasheet to a SQL Server table then executes
a user Stored Proc
What I am wanting to do is if for some reason the Stored Proc errors
out because of bad data , I would like to see the errors reported in a
file so that it can be reviewed and I was not sure if there is a way to
output the errors in DTS. When I run the Stored Proc theough Query
Analyzer I see the errors in Query Analyzer, I basically want to see
the same information after running the DTS. Can this be done, if so
how.
Any help in this regard is greatly appreciated.
Thanks|||I would use the DTSRUN command to execute the DTS package. On the DTSRUN
command you can use the /L option to create a log froma DTS package.
"shub" wrote:

> I am using SQL Server 2000 and I have a created a DTS package that
> copies Data from a Excel Spreasheet to a SQL Server table then executes
> a user Stored Proc
> What I am wanting to do is if for some reason the Stored Proc errors
> out because of bad data , I would like to see the errors reported in a
> file so that it can be reviewed and I was not sure if there is a way to
> output the errors in DTS. When I run the Stored Proc theough Query
> Analyzer I see the errors in Query Analyzer, I basically want to see
> the same information after running the DTS. Can this be done, if so
> how.
> Any help in this regard is greatly appreciated.
> Thanks
>|||Thanks Greg for your response. I will definitely look at that option.
Is there any way this option could be incorporated when executed
through enterprise manager.
Greg Larsen wrote:[vbcol=seagreen]
> I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> command you can use the /L option to create a log froma DTS package.
> "shub" wrote:
>|||I don't know of any way, sorry.
"shub" wrote:

> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
>|||Hi Greg,
Yes, if you open up your DTS package and go to Package => Properties you
will see a tab for 'Logging', in the 'Error Handling' section you can specif
y
a file to log to. Just remember that the file is always appended to and not
overwritten.
Ray
"shub" wrote:

> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
>|||Hi Greg,
Yes, if you open up your DTS package and go to Package => Properties you
will see a tab for 'Logging', in the 'Error Handling' section you can specif
y
a file to log to. Just remember that the file is always appended to and not
overwritten.
Ray
"shub" wrote:

> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
>|||Thanks Ray. I tried using that however when there are multiple errorrs
it is displaying only the first error. For example in my case the DTS
package executes a stored proc to add logins from the table but in some
cases because of typos the proc cannot grant access because it cannot
find the user account in the domain, but if there are multiple errors
it is displaying the very firts one but when I run the same stored proc
through Query analyzer I see all the errors and I need to see all the
errors so that it can be informed that there are wrong entries in the
table.
Any ideas? Here is the only error I am getting
Step 'DTSStep_DTSExecuteSQLTask_2' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
(Microsoft OLE DB Provider for SQL Server (80040e14): Windows NT user
or group 'YYY\XXX' not found. Check the name again.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
****************************************
************************************
************************
rb wrote:[vbcol=seagreen]
> Hi Greg,
> Yes, if you open up your DTS package and go to Package => Properties you
> will see a tab for 'Logging', in the 'Error Handling' section you can spec
ify
> a file to log to. Just remember that the file is always appended to and no
t
> overwritten.
> Ray
> "shub" wrote:
>

DTS Problem

Hi,
Can any one tell me how can i use and from where i can study about
execute sql task in dts.
I have a problem that i have serverA and a OLAP Server same as my
server A.
I had scheduled a job to transfer data on nighlty basis.Some time my
team memeber(Programmer) adds a field in a table on server A . When my
job executes on olap server it gives error and fails.
The error occurs because of column not found in Table of OLAP Server.
To remove the error i have to manually increase the field in that
table.
Later on i use drop table and select * into table name from
database.username.tablename
But i think this is not a best way to do.
Can any one suggest me what should i do in this case.
from
killerHi
There is good documentation on DTS in Books online, which should be in your
SQL Server programs group on the start menu. You may want to check you have
the latest version or download it from
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
There is also a very good site at www.sqldts.com which has many examples and
articles.
It is not a good idea to use SELECT * in production code as you it can break
(as yours does) if some changes the columns.
John
"doller" wrote:
> Hi,
> Can any one tell me how can i use and from where i can study about
> execute sql task in dts.
> I have a problem that i have serverA and a OLAP Server same as my
> server A.
> I had scheduled a job to transfer data on nighlty basis.Some time my
> team memeber(Programmer) adds a field in a table on server A . When my
> job executes on olap server it gives error and fails.
> The error occurs because of column not found in Table of OLAP Server.
> To remove the error i have to manually increase the field in that
> table.
> Later on i use drop table and select * into table name from
> database.username.tablename
> But i think this is not a best way to do.
> Can any one suggest me what should i do in this case.
> from
> killer
>|||So, What should be done so that i can come to know that i have to
increase the field or how can i automaticaly schedule it.
from
killer
John Bell wrote:
> Hi
> There is good documentation on DTS in Books online, which should be in your
> SQL Server programs group on the start menu. You may want to check you have
> the latest version or download it from
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
> There is also a very good site at www.sqldts.com which has many examples and
> articles.
> It is not a good idea to use SELECT * in production code as you it can break
> (as yours does) if some changes the columns.
> John
> "doller" wrote:
> > Hi,
> > Can any one tell me how can i use and from where i can study about
> > execute sql task in dts.
> >
> > I have a problem that i have serverA and a OLAP Server same as my
> > server A.
> > I had scheduled a job to transfer data on nighlty basis.Some time my
> > team memeber(Programmer) adds a field in a table on server A . When my
> > job executes on olap server it gives error and fails.
> > The error occurs because of column not found in Table of OLAP Server.
> > To remove the error i have to manually increase the field in that
> > table.
> > Later on i use drop table and select * into table name from
> > database.username.tablename
> > But i think this is not a best way to do.
> >
> > Can any one suggest me what should i do in this case.
> >
> > from
> > killer
> >
> >|||Hi
This is a code management issue. Making the columns will mean it won't
break, but if you are adding the extra column you should know from the change
analysis that you are going to need to change the job to populate the table,
adding the extra column when it was not needed would not help performance.
John
"doller" wrote:
> So, What should be done so that i can come to know that i have to
> increase the field or how can i automaticaly schedule it.
> from
> killer
>
> John Bell wrote:
> > Hi
> >
> > There is good documentation on DTS in Books online, which should be in your
> > SQL Server programs group on the start menu. You may want to check you have
> > the latest version or download it from
> > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
> >
> > There is also a very good site at www.sqldts.com which has many examples and
> > articles.
> >
> > It is not a good idea to use SELECT * in production code as you it can break
> > (as yours does) if some changes the columns.
> >
> > John
> >
> > "doller" wrote:
> >
> > > Hi,
> > > Can any one tell me how can i use and from where i can study about
> > > execute sql task in dts.
> > >
> > > I have a problem that i have serverA and a OLAP Server same as my
> > > server A.
> > > I had scheduled a job to transfer data on nighlty basis.Some time my
> > > team memeber(Programmer) adds a field in a table on server A . When my
> > > job executes on olap server it gives error and fails.
> > > The error occurs because of column not found in Table of OLAP Server.
> > > To remove the error i have to manually increase the field in that
> > > table.
> > > Later on i use drop table and select * into table name from
> > > database.username.tablename
> > > But i think this is not a best way to do.
> > >
> > > Can any one suggest me what should i do in this case.
> > >
> > > from
> > > killer
> > >
> > >
>

DTS Pkg Job error

I recently installed a new SQL Server and am having problems scheduling my DTS packages. They will work fine if I manually execute them, but none of them will fire from a job.

Regardless of the DTS package scheduled it fails with the following error:

Executed as user: STANDREWS\SYSTEM. The process could not be created for step 1
 of job 0x8D49E4684F14CB49BFC9223F4E47FC16 (reason: The system cannot find the file
specified). The step failed.

I have tried all kinds of stuff to no avail.What does step 1 look like? Did you try specifying the entire path for dtsrun.exe?

Terri|||when they fire from a job, they are firing under the security context of the account that the SQL Agent uses.

Therefore i would conclude that your account has access to something that the SQL Agent account does not.

you could set the SQL Agent to use a specific machine account...|||Yeah, created the job in various fashions, by name, by GUID, etc, etc. I am not certain of the absolute file path though, where is that found?

I will also check the SQL Agent security.|||The absolute file path is the physical path to dtsrun.exe on the SQL Server. On my local SQL Server, e.g., it can be found here: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe

I am bringing this up because I have come across this problem in the past.KB 322746 - PRB: Data Transformation Services Jobs May Fail Because the Path for Dtsrun.exe Cannot Be Found addresses this.

Terrisqlsql

DTS Package to VB

One can save a DTS Package to a VB .bas file. Can you modify the VB
code and then reload the package and execute it? I can see an option to
save a package to vb but can't see an option to open a vb file as a
package.
RonI'm using .NET and could not get the VB program to compile using the
example in the DTS Help. I was getting a "Statement is not a valid
namespace" for the following statements:
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
I work with C# and Java and am not that familiar with VB.NET.
Any help appreciated.
Ron|||hi,
As far as I know it is not possible. As you said, oneself is be able to save
into .BAS file and then modify and save it again and even executing from VB
6.0 using dtspkg.dll. Why don't open it through sql server 2000/2005?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"RonL" wrote:

> I'm using .NET and could not get the VB program to compile using the
> example in the DTS Help. I was getting a "Statement is not a valid
> namespace" for the following statements:
> Public goPackageOld As New DTS.Package
> Public goPackage As DTS.Package2
> I work with C# and Java and am not that familiar with VB.NET.
> Any help appreciated.
> Ron
>

Dts package that imports difference

Assuming you are using an Execute SQL task, you can use a script like the
one below:
SET IDENTITY_INSERT Database2.dbo.MyTable ON
GO
INSERT INTO Database2.dbo.MyTable
(
ID,
MyData
)
SELECT
ID,
MyData
FROM Database1.dbo.MyTable AS t1
WHERE NOT EXISTS
(
SELECT *
FROM Database2.dbo.MyTable AS t2
WHERE t2.ID = t1.ID
)
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have to server and two different database on them. I want to import one
> table from database1 to database2. But I want to import only difference
> between two table.I want to also keep identity column value. I created a
> dts package but I don't know how can I put a condition like "where myid
> not in(Select myid from database2.table)" and where can I put Set
> IDENTITY_INSERT mytable ON?
> Thanks in advance,
>
>
in DTS,
first you have to get the lastet ID (select max(id) from destination)
then you have to dynamically create the select statement (Select * from
source where id > maxid)
(or you have to use a parametrized query where the parameter will be the ID)
and finally load the data.
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>
|||>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
You are correct. It was unclear to me from your message that the databases
were on different servers.
If you have a lot of changes, I suggest you copy the data from the source
table into a staging table on the target databse server and then use an
Execute SQL task.
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>
|||Hi
"italic" wrote:

> I don't think you can connect from "Execute SQL task" to both server. You
> need to select connection(server) first. Am I wrong?
> Thanks
You can create a linked server and reference that in your query, or as you
have a second connection to the destination server you could use a query
(task) to retrieve the highest identity from the destination server then use
that in a global variable as the start of the information retrieved from the
second server.
See http://www.sqldts.com/280.aspx
http://www.sqldts.com/205.aspx
The second method would not necessarily be transactionally consistent,
therefore you should not be changing the destination table when this is
running.
John
|||Hi
"italic" wrote:

> Linked Server is not allowed. I can try the second way.
> Thanks
>
You would need a 4 part name! What error do you get?
John
sqlsql

DTS package step to disable

Hi DBA's,

I have a DTS package that has steps 1 to 13. Say for a given situation I do not want the package to execute Step 11, 12 and 13 but steps 1 to 10 should be executed and the package should be treated as a sucess. Please confirm if the following approach is correct.

1. I go to Local Packages on the EM and then open up my package in designer view.
2. I go to step 11, right-click to go to WorkFlow properties. I go to Options tab and under execution I select disable this step.
3. I repeat the process for step 12 and step 13. Now 11 to 13 are disabled. I now execute the package.

Is the above correct?That will work in your description - but that is very hardwired - when would you not execute those steps ?|||Originally posted by rnealejr
That will work in your description - but that is very hardwired - when would you not execute those steps ?

Well step 11 is a stored proc which is failing now in the DTS package. So I want to disable step 11 and all the further steps. I intend to run step 11 in the QA. I tried to automate it by includusing step 11 in a DTS package. I am concerned with only step 1 to step 10. the rest of the steps 11-13 can be done manually.

DTS Package scheduled to run as Job Fails

Hi,

I have a DTS package which when I execute it manually, it executes perfectly. I need to schedule it and therefore had to use the scheduler which would creata a job for the package. The problem is that job. It does not execute; it fails every time. The error message is: Sql server does not exist or access is denied.

I have tried to trouble-shoot by setting up an alias for the server in the client network utility for sql server. I also create a new dsn with the new alias. It still failed with the same error message. I edited the properties of the dts package to connect to the server's ip instead of it's alias or logical name. Still fails.

Any ideas anyone?

MariaIs sa the owner of the job ? If not make sa the owner

DTS Package reports error when executed from an ASP page

I'm not sure that this is the correct place to ask this but please bear with me. I am trying to execute a DTS package from an ASP page on the web server but the package fails and returns the error "invalid file path".

The package connects to a remote folder on the LAN and uploads a couple of foxpro tables to SQL Server. The package runs without errors when executed from Enterprise Manager.

I have an inkling the problem is to do with permissions but I'm not sure. Is it true that when run from a web page, the DTS is executed by the webserver? If so, how can I modify the access permissions of the remote folder so as to grant IUSR_WebServerName Read and Write access?

Any help is much appreciated.

Yes it is true that the DTS package will be executed by the Webserver, with whatever permissions that service account has. I am sorry, but I do not know enough about the web server end of the operation to tell you how to give the webserver access to that folder.

Donald Farmer

|||

Thanks for the reply. I have solved the problem by using COM objects to wrap the DTS and runing it from a stored procedure.

|||

Thanks. Good to hear you have it working.

DTS Package Problem

I have a stored procedure that calls a DTS package using xp_cmdshell dtsrun. The problem is strange, I can execute the stored procedure from Query Analyzer and it works fine. I call the stored procedure from inside of an ASP.NET application and it seems to execute but doesn't. The user account for the ASP.NET application has rights to execute the stored procedure and I have run a trace and so see any login failures. I'm stumped, any clues??

neel

xp_cmdshell can run an enterprise but it is a SQL Server Agent dependent service, you have to install SQL Server Agent with a service account to run all the dependent services. Hope this helps|||

Does the SQL Server agent have to be on the client computer or just the computer with SQL Server on it. I would think since I'm just sending the stored procedure name of the stored procedure on SQL Server that that server would be the only place I need the SQL Server Agent?

Thanks

Neel

|||The SQL Server Agent in the computer executing xp_cmdshell needs a service account because you can schedule xp_cmdshell with a job to transfer data from main frame to another SQL Server in the same network and it will run it. SQL Server Agent needs a service account if your application will use any of its dependent services like DTS Automation, Replication and jobs. Microsoft keep this from developers because there is a lot to know in development. Hope this help|||

Thanks it is working now. I also had to go in and setup a proxy account in the SQL Server Agent that had sysadmin rights because the users of the program and the program user account do not have sysadmin rights.

Thanks Again

Neel

dts package failure when setup as job

HI I have a dts package (SQL 2000) that I can execute from Enterprise manager
opened on a client machine connected to a server. When I schedule it as a
job though Enterprise manager on the client machine connected to the server
the job fails. The package connects to an SQL database on the server as well
as an oracle database on another server. On job failure the event is written
to the windows event log on the server. The error message is Source SQL
Server agent,
status failed, the job failed.
I am thinking it may have to do with credentials, any ideas how to
troubleshoot this?
thanks.
Paul G
Software engineer.
Paul,
Yes, when you run it from Enterprise Manager, you are running under your
credentials. A scheduled job has different credentials.
When you schedule the DTS package from SQL Agent, it will run either as the
server account or as the account defined in xp_sqlagent_proxy_account for
your server. The SQL Server 2000 Books Online article on
xp_sqlagent_proxy_account has a fairly clear explanation.
Once you determine which credentials your job is using, then you can have
appropriate access granted to that account.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
> HI I have a dts package (SQL 2000) that I can execute from Enterprise
> manager
> opened on a client machine connected to a server. When I schedule it as a
> job though Enterprise manager on the client machine connected to the
> server
> the job fails. The package connects to an SQL database on the server as
> well
> as an oracle database on another server. On job failure the event is
> written
> to the windows event log on the server. The error message is Source SQL
> Server agent,
> status failed, the job failed.
> I am thinking it may have to do with credentials, any ideas how to
> troubleshoot this?
> thanks.
> --
> Paul G
> Software engineer.
|||ok thanks for the information. Another problem is part of the package writes
to an Oracle database but the oracle client software is not installed on the
server.
Paul G
Software engineer.
"Russell Fields" wrote:

> Paul,
> Yes, when you run it from Enterprise Manager, you are running under your
> credentials. A scheduled job has different credentials.
> When you schedule the DTS package from SQL Agent, it will run either as the
> server account or as the account defined in xp_sqlagent_proxy_account for
> your server. The SQL Server 2000 Books Online article on
> xp_sqlagent_proxy_account has a fairly clear explanation.
> Once you determine which credentials your job is using, then you can have
> appropriate access granted to that account.
> RLF
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4E204B8C-1174-4713-A420-273E72F92178@.microsoft.com...
>
>
|||Paul,
So that is the other part. When you run a DTS package from Enterprise
Manager in runs on the same machine that Enterprise Manager is running on.
So, if you are running EM on your desktop, that is where DTS runs as well.
Naturally if you have the Oracle client software on your desktop, it will
work for you.
If that is the case, then you will need to evaluate putting the Oracle
client software on the SQL Server. I don't have any experience with Oracle,
but plenty of people have successfully used DTS, Linked Servers, etc between
SQL Server and Oracle Server.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...[vbcol=seagreen]
> ok thanks for the information. Another problem is part of the package
> writes
> to an Oracle database but the oracle client software is not installed on
> the
> server.
> --
> Paul G
> Software engineer.
>
> "Russell Fields" wrote:
|||thanks for the additional information. I was wondering why it worked from my
client machine, but I do have the Oracle client software installed on it. I
had it on the server before but we ran into an issue with Appachi effecting
IIS so someone removed all of the Oracle software.
Paul G
Software engineer.
"Russell Fields" wrote:

> Paul,
> So that is the other part. When you run a DTS package from Enterprise
> Manager in runs on the same machine that Enterprise Manager is running on.
> So, if you are running EM on your desktop, that is where DTS runs as well.
> Naturally if you have the Oracle client software on your desktop, it will
> work for you.
> If that is the case, then you will need to evaluate putting the Oracle
> client software on the SQL Server. I don't have any experience with Oracle,
> but plenty of people have successfully used DTS, Linked Servers, etc between
> SQL Server and Oracle Server.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:C5E14CA8-6327-4F64-854B-2C08274CE61D@.microsoft.com...
>
>