DTS problem in SQL Server 2005

[SQLBill]

I have a developer that is fighting going to SSIS, they are more comfortable with DTS and I have to support it.

They have a DTS package that gets data from an Access database, transforms it, and puts it into a SQL Server 2005 database. This package works fine for them when they execute it. I can execute the package and have it work. But if it is in a job, it is failing and I haven't been able to figure out the solution. The error is:

Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2007-03-09 05:00:02.21 Code: 0xC0011002 Source: {570F75FF-0EB6-4565-9B2D-237727B1755D} Description: Failed to open package file "Update Data from Access.dts" due to error 0x800C0006 "The system cannot locate the object specified.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "Updat. The step failed.

Has anyone else had this issue and been able to resolve it? It's strange (to me) that I can run the DTS package by itself and it works, but if I run the job it fails.

-SQLBill


Posting advice: FAQ481-4875

 

[onpnt]

The job is going to run under a different account (the service) if I recall the way it is executed differently from a manual start and a scheduled run. Double check and make sure the credentials that the agent is running under have permissions to all the files etc. that are required for the job to be successful


[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[fuzzyocelot]

Is the database local to the computer or on a server? Meaning, is the instance you're connected to on a server or on a workstation? Also, are you using the tools (i.e. Management Studio) locally or on the server?

I don't know if you've already taken this into consideration or not. If you're using the tools on your computer and run the DTS package manually, it will use the files on your computer (i.e. the Access db). When you execute a job on an instance that is on a server, it will use the files on the server. Does this make sense? I'm trying to explain it without being confusing. So it could depend on your setup. Meaning, if the instance you're connected to is on the server then see if putting the db on the server helps.

 

[SQLBill]

The Access database is not on the same server as the SQL Server database. However, that is taken care of in the DTS package. The SQL Server Agent login has full access to the file.

The DTS package is on the SQL Server machine.

-SQLBill

Posting advice: FAQ481-4875

 

[fuzzyocelot]

When you run the package manually, are you logged onto the server where the database resides or are you doing it remotely?

At my work place if the source file within a package is on another server on our network, I am unable to run it via a job but I can run it manually (and remotely) from my computer because my computer is mapped to it. Can you see where I'm trying to get at? Where is the Access file located at? It's just a thought.

 

[oracleSQLdba]

Did it ever work?

 

[SQLBill]

The owner of the DTS can run it manually remotely, I can run it manually logged into the server. We just can't run it as a job.

The Access file is on the developer's computer. But we have other SQL Server's DTS packages that do the same thing (access a file on another server from the SQL Server server). We aren't finding anything different in the coding.

And nope, this still isn't working....I'm testing different ideas daily.

-SQLBill

Posting advice: FAQ481-4875

 

[onpnt]

Not sure it will help but when this type of situation happens to me I log into a table at every step starting from the job call what credentials are being used. I have a DBA database setup for these types of things. It always turns out to be credentials being used at a particular step. It's kind of a half a$$ line break

If you can get them to just do it as a SSIS like they should you can debug the package pretty well

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[mrdenny]

Bill,
Time to go back to basics. When you are running it manually from the server are you logged in as the SQL Server or as your self?

Are there any strange DTS objects being loaded (shouldn't be as you can run it, but you never know)?

Does the account running the job have sysadmin rights to SQL, administrator rights to windows?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

Bill,
Time to go back to basics (not to say you didn'y already check this stuff, but it's my job to ask right). When you are running it manually from the server are you logged in as the SQL Server or as your self?

Are there any strange DTS objects being loaded (shouldn't be as you can run it, but you never know)?

Does the account running the job have sysadmin rights to SQL, administrator rights to windows?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[onpnt]

I think Denny was a victim of the <. disease

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[SQLBill]

I have run it manually logged in both as myself and as the SQL Server agent. They were successful.

No strange objects being loaded.

The account has sysadmin on SQL Server and Windows, plus the developer shared the file to everyone.

This is stumping me because I have several other DTS packages on 2005 that are similar and work just fine.

At this point, I think I'm going to delete the package and have the developer start over again. (Preferably using SSIS).

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Just to let you all know....this is being dropped. We have been told that since MS does not really support DTS anymore, neither will we. Coders must start using SSIS solutions.

Thanks for all the ideas, suggestions.

-SQLBill

Posting advice: FAQ481-4875

 

[onpnt]

Good call. Coming in the next release you will not have the ability to run them anyhow (that's what they say) so you're going the right way

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]