Web Development

Ask something
Discussion Locked

SQL DTS package can't run at server after changes

0 helpful
9 replies

by paulkayengheng

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have made changes to a DTS package at my laptop, which have accessed to the SQL 2000 server.

I am able to execute this package. But when I run this package as a scheduled job in SQL Agent, this package failed with error message "Error opening datafile. Logon invalid user or password wrong".

I also got this error when I manually rerun this package at the server.

9 replies

by kevin.lobo

The errors you are getting are due to permission issues.
Ask your DBA or someone with SA priveleges on the SQL Server to change the ownership of the scheduled job/package to SA or a similar level login.

by paulkayengheng

Do I need to make this change every time a user changes or updates a package? Is there any other solution to this problem?

by softwareguru

(Assuming that your datafile is an external db like MS Access or text file)
SQL Agent Service usually runs as a local system account. This account doesn't have access to any of the network resources. So, if your data file is located on a network drive SQL Agent won't be able to access the file. To correct this problem, either copy the file to the SQL machine or create the same admin account (same password)on the two machines and then run SQL Agent with that account.
Good Luck.
CJ

by paulkayengheng

How do I check the present SQL Server ownership of the scheduled job or package? How do I go about to change the SQL Server ownership for them?

I am running linked servers. Using DTS to call Excel with a Update macro to update a spreadsheet from a text file. The spreadsheet is located at the linked server. Whenever the package is failed/cancelled, the task is not terminated. This will lead to our users unable to amend the spreadsheet manually. They received a message 'read-only' access.

by traty1

This has nothing to do with package ownership. When you manually run a dts, it uses your credentials. When you schedule the dts as a job it uses the sql agent. Whatever account you have given the sql agent, make sure this account has all the rights and permissions needed to access whatever datafiles, folders, and drives your dts uses. For instance, if your dts grabs data from an Access database on server A drive X, ensure the sql server agent account has permissions to the database file on server A drive X.

by paulkayengheng

Look like I have a "Double hop" problem by Microsoft(MS). MS
provided the solution saying:

"Map the clients on server A to a standard security login on server B, by using either the "sp_addlinkedsrvlogin" stored procedure or the Security tab of the Linked Server Properties dialog box in Enterprise Manager."

Have anyone out there used these options before? I would like a sample script or more deatil steps.

by mpeer

I've had once the same problem to. And the problem was that the DTS package could not run because my local computer (with only the client tools) had SQL Service Pack 3, while the server still had Service Pack 1 or 2. Upgrading the sql server to service pack 3 solved the problem.

Note: packages with SP3 do have a diferent binairy layout than the previous SP's.

This discussion is closed to new replies.