DTS schedult problem

I created a DTS package to transfer a table to a text file. Every time when
I click start manually, it'll run and transfer the data to a local txt file
with no problem. But when I schedule this DTS package and run it from the
SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can help
me?|
| I created a DTS package to transfer a table to a text file. Every time
when
| I click start manually, it'll run and transfer the data to a local txt
file
| with no problem. But when I schedule this DTS package and run it from the
| SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
| me?
--
We need more information to help you. What errors are returned when the job
fails? Post it and we'll give it a shot.
Thanks,
--
Eric Cardenas
SQL Server support|||JL
I think that you cerated DTS package on the workstation not on the server ,
then SQL Agent on your wokstation is running under differnent account than
server.
Check it out.
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eGGt1AgvDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I created a DTS package to transfer a table to a text file. Every time
when
> I click start manually, it'll run and transfer the data to a local txt
file
> with no problem. But when I schedule this DTS package and run it from the
> SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
> me?
>|||VXJpIGlzIGNvcnJlY3QuICBJIHJhbiBpbnRvIHRoaXMgcHJvYmxlbSBhbmQgaXQgaGFkIG1lIHN0
dW1wZWQgZm9yIGEgd2hpbGUuDQoNClRoZSBzb2x1dGlvbiBpcyB0byBjcmVhdGUgdGhlIERUUyBw
YWNrYWdlIGFuZCBKb2Igd2hpbGUgcGh5c2ljYWxseSBhdCB0aGUgU1FMIFNlcnZlciBib3guDQoN
Cg0KIkpMIiA8bGptYWd6aW5lQGhvdG1haWwuY29tPiB3cm90ZSBpbiBtZXNzYWdlIG5ld3M6ZUdH
dDFBZ3ZESEEuMjMwNEBUSzJNU0ZUTkdQMTIucGh4LmdibC4uLg0KPiBJIGNyZWF0ZWQgYSBEVFMg
cGFja2FnZSB0byB0cmFuc2ZlciBhIHRhYmxlIHRvIGEgdGV4dCBmaWxlLiBFdmVyeSB0aW1lIHdo
ZW4NCj4gSSBjbGljayBzdGFydCBtYW51YWxseSwgaXQnbGwgcnVuIGFuZCB0cmFuc2ZlciB0aGUg
ZGF0YSB0byBhIGxvY2FsIHR4dCBmaWxlDQo+IHdpdGggbm8gcHJvYmxlbS4gQnV0IHdoZW4gSSBz
Y2hlZHVsZSB0aGlzIERUUyBwYWNrYWdlIGFuZCBydW4gaXQgZnJvbSB0aGUNCj4gU1FMIEFnZW50
IC0gSm9icy4gSXQgYWx3YXlzIGZhaWwgdG8gcnVuLiBJIGRvbid0IGtub3cgd2h5LiBBbnlib2R5
IGNhbiBoZWxwDQo+IG1lPw0KPiANCj4gsqlsql

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 Package problem?

I have set up a couple of DTS local packages to run ActiveX scripts creating XML files and copying them to our webserver (on the same domain).

I can run them OK manually via the 'Execute' package command in the drop down list when I right click on them and also get the 'Package successfully run' message back on running them, but when I schedule them to run overnight I find the SQL Server Agent reports them as 'failed' .

What am I doing wrong?

This looks like a permissions issue...do you have a proxy account setup for the SQL Agent? Look here for info...http://www.support.microsoft.com/kb/890775

Brad Feaker

Ex nihilo, nihil fit

|||As referred if you are scheduling the DTS package then make sure to check privileges for the SQLAgent service account on those process, you can start the SQLAgent using a local account with Admin privleges to complete the task.

DTS Package Owner

I have to modify the DTS package owner that is displayed in the local
packages view, the current owner no longer works for the company so I need
to reassign ownership. I am not a sql expert by any means.
Can anyone help me with this?
It is SQL Server 2000 sp4 running on Windows 2003 sp1.
--
Paul Bergson
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Paul - please take a look here: http://www.sqldts.com/default.aspx?212
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||This is what I was looking for.
Thanks
--
Paul Bergson
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ulsvIajEHHA.992@.TK2MSFTNGP03.phx.gbl...
> Paul - please take a look here: http://www.sqldts.com/default.aspx?212
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>

DTS package manually/locally it runs fine. But the scheduled job run and fails

I was facing some issue on MP. Yesterday I changed the SQL services to use the local admin account. That didn't help my MP issue. But it may have created another issue.
When I run the package manually/locally it runs fine. But the scheduled job run and fails with the Title string.
----------
Executed as user: IL06EDM00\SYSTEM. ...tart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with ... Process Exit Code 2. The step failed.
-------------------

Any ideas.is this Gary down the hall?

more than likely your DTS package is accessing something on another machine. When you run the DTS package, it runs with your security credentials. When the agent is running it, it uses the local system account which does not have network access. Change your sql services to run on a domain account that has access to places DTS needs to do stuff.

DTS not running on a mapped drive

I created a DTS local package on the SQL Server. It's basically importing a text file into a table in my database. This file resides in a mapped drive (X:) from another server. When I schedule the DTS as a job, it fails. It doesn't execute any of the workflow in the design.

However, when I copy the text file into a drive local to the SQL server (D:), it runs flawlessly.

What I do right now is I have a windows scheduled task that runs a batch file that copies the text file from X: to D: at certain time intervals. Then the job scheduler runs to import it.

What am I missing? How come the job scheduler can't read the file directly from the mapped drive?

Any info would be appreciated. TIAWhat am I missing? How come the job scheduler can't read the file directly from the mapped drive?

Any info would be appreciated. TIA

A mapped drive only exists in the context of your login session. You will need to use a UNC path to reference the file:

\\MyServer\ServerShare\Path\FileName.txt

Alternatively (NOT recommended), you can use the command NET USE, but you are much better off using UNC.

Regards,

hmscott|||Thanks - that worked for me.

DTS Job fails when scheduled

I have a DTS local package that transfer data from the host SQL server to another. If I run the package directly (i.e. Right click - Execute Package) everything works as it should.

However, if I try and execute this as a Server Agent job it fails, the job history step details shows the following error:

Executed as user: MYSERVERNAME\SYSTEM. ...: Drop table customer Step DTSRun OnError: Drop table customer Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: Drop table customer Step DTSRun OnStart: Create Table customer Step DTSRun OnError: Create Table customer Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server... Process Exit Code 2. The step failed.

Whilst this is obviously a connection issue, I dont understand how this can work if executed directly and not if executed as an agent job.

Can anybody shed any light on this?

If it helps this is the sequence of events my DTS package uses

1) Connect to remote DB
2) Drop table customers
3) Create new remote customers table
4) Connect to to local DB (hosting the DTS package)
5) Select * from local customers table
6) Dump data from local select into remote DBIt's a frequently asked question. When you open a DTS package from your workstation, the environment is that of your workstation.

When you execute a DTS package from a scheduled job, the environment is that of the server.

If there are differences between the server environment and the environment of your workstation, then the executed package will fail.

For example, if you have Oracle drivers configured on your PC (but not on the server) you will be able to execute the DTS package from your PC, but not from the server.

Try using Terminal Services to remote in to the DB Server. Then open the package and execute it from the server desktop. The errors become more apparent at that point.

Regards,

hmscott|||If you allow mixed mode authentication, create an login on your target server for use as a service account, then define credentials for your individual connections in the "Connection Properties" dialog for each specific server connection you intend to use.

Alternately, you can setup your target server as a linked server and specify which credentials it is to use when operating on the remote server in the "Security -> Linked Servers" node in Enterpise Manager.

DTS Import/Export Wizard database copy problem

Hi

I'm experiencing problems copying a SQL 2000 database from my host to a local SQL Server.

I choose the Data Source OK, using SQL Server Authentication and the username and password used to setup my hosted database.

Then I specify the destination, my local server. I'm using my sa account and password and creating a <new> database (doesn't matter if I use the same db name as the source or a different one, I'm still getting an error).

I then choose "Copy objects and data between SQL Server databases", then take all defaults on the "Select Objects to Copy screen", i.e. create destination objects drop objects first and include dependent objects); creat data (replacing existing data); Copy all objects and use default options.

The copy appears to be creating a script OK (runs up to 100%), then looks to start copying objects. It gets to approx 20% and reports that it "Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server". When I click on the Executing Package entry it tells me:

[Microsoft][ODBC SQL Server Driver][SQL Server]The login already has an account under a different user name.

I can't seem to find any reference to this error in my books or on Google (usually a great source of info). Can't help feeling I'm doing something stupid. Would be very, very greatful if someone out there could give me a pointer in the right direction.

Thanks,
Keith.On the "Select Objects to Copy screen" I would uncheck the "Use default options" box at the bottom, click "Options..." and uncheck "Copy database users and database roles". This should take care of the problem.

Terri