DTS scheduling issues

[Dallas]

This is kind of a hybrid issue. When I or my developers create DTS packages we can usually run them fine from our machines, but when we go to schedule them, or run them from the Server Client we get an error that the package cannot be found. I realize that this must be some time of permissions or owner issue, but I don't know where to start looking. Should all of my DTS packages be owned by the SQL Agent Account? What am I overlooking?

[Diana]

If you output your job outcome to a text file on failure, does it look like this?

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1
DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147024713 (800700B7)
Error string:
Error source:
Help file:
Help context: 0

Error Detail Records:
DTSRun OnFinish: DTSStep_DTSTransferObjectsTask_1
DTSRun: Package execution complete.




------------
Dallas at 7/26/01 3:24:10 PM

This is kind of a hybrid issue. When I or my developers create DTS packages we can usually run them fine from our machines, but when we go to schedule them, or run them from the Server Client we get an error that the package cannot be found. I realize that this must be some time of permissions or owner issue, but I don't know where to start looking. Should all of my DTS packages be owned by the SQL Agent Account? What am I overlooking?

[Diana]

In other words, are you referencing files on a remote server?


------------
Diana at 7/26/01 3:44:01 PM

If you output your job outcome to a text file on failure, does it look like this?

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1
DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147024713 (800700B7)
Error string:
Error source:
Help file:
Help context: 0

Error Detail Records:
DTSRun OnFinish: DTSStep_DTSTransferObjectsTask_1
DTSRun: Package execution complete.




------------
Dallas at 7/26/01 3:24:10 PM

This is kind of a hybrid issue. When I or my developers create DTS packages we can usually run them fine from our machines, but when we go to schedule them, or run them from the Server Client we get an error that the package cannot be found. I realize that this must be some time of permissions or owner issue, but I don't know where to start looking. Should all of my DTS packages be owned by the SQL Agent Account? What am I overlooking?

[Dallas]

Yes, sorry for being general and obtuse in that last send, but what I have is a developer who wrote a dts package that involves pulling data from an application SQL server to the Data warehouse. I haven't tried an output to text, but I can. Like I was saying the job runs fine, if you run it from where the DTS package was created (my machine last) but if I log on the server and try to run it, it says "the system cannot find the file specified."
I didn't even take the remote server piece into consideration. How do I schedule a job? Thanks, Dallas


------------
Diana at 7/26/01 3:45:07 PM

In other words, are you referencing files on a remote server?


------------
Diana at 7/26/01 3:44:01 PM

If you output your job outcome to a text file on failure, does it look like this?

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1
DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147024713 (800700B7)
Error string:
Error source:
Help file:
Help context: 0

Error Detail Records:
DTSRun OnFinish: DTSStep_DTSTransferObjectsTask_1
DTSRun: Package execution complete.




------------
Dallas at 7/26/01 3:24:10 PM

This is kind of a hybrid issue. When I or my developers create DTS packages we can usually run them fine from our machines, but when we go to schedule them, or run them from the Server Client we get an error that the package cannot be found. I realize that this must be some time of permissions or owner issue, but I don't know where to start looking. Should all of my DTS packages be owned by the SQL Agent Account? What am I overlooking?

[Jeff]

Hi Dallas,

This same issue has caused me to hit the board tonight.

I found the following on MS TechNet.

Q. Has anyone encountered a problem editing/saving a package from a client machine
using SP2 to the server running SP1 then trying to schedule that package?

A. The sp2 readme section 5.10.6 says sp2 changed the internal
format that DTS uses to save packages. Computers running SP1 or earlier versions
of DTS cannot read a package [created] using the sp2 version of DTS.


I home a can verify but this stands a good chance of being my problem as
some of our system have not been upgraded.

Also found this:

When you run apackage using the SQL Server Agent, packages run in the SQL Server
Agent context. Therefore, the SSA must have permissions to run the package
(access databases, files, etc.


Hope this is of help.

Jeff


------------
Dallas at 7/26/01 3:56:29 PM

Yes, sorry for being general and obtuse in that last send, but what I have is a developer who wrote a dts package that involves pulling data from an application SQL server to the Data warehouse. I haven't tried an output to text, but I can. Like I was saying the job runs fine, if you run it from where the DTS package was created (my machine last) but if I log on the server and try to run it, it says "the system cannot find the file specified."
I didn't even take the remote server piece into consideration. How do I schedule a job? Thanks, Dallas


------------
Diana at 7/26/01 3:45:07 PM

In other words, are you referencing files on a remote server?


------------
Diana at 7/26/01 3:44:01 PM

If you output your job outcome to a text file on failure, does it look like this?

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1
DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147024713 (800700B7)
Error string:
Error source:
Help file:
Help context: 0

Error Detail Records:
DTSRun OnFinish: DTSStep_DTSTransferObjectsTask_1
DTSRun: Package execution complete.




------------
Dallas at 7/26/01 3:24:10 PM

This is kind of a hybrid issue. When I or my developers create DTS packages we can usually run them fine from our machines, but when we go to schedule them, or run them from the Server Client we get an error that the package cannot be found. I realize that this must be some time of permissions or owner issue, but I don't know where to start looking. Should all of my DTS packages be owned by the SQL Agent Account? What am I overlooking?

[Diana]

Dallas -- I found the same problem (with the output that I duplicated in my first response). I could run it as a DTS package, but when I tried to schedule it, I would always get that output. I was using the "transfer objects" of DTS. What DTS would do is to create SQL scripts to run the package. When the scripts were placed on a local drive on the server, the job ran fine. What I suggest is to either map a permanent drive to the remote server -- you might also try using the Linked Servers option in EM.


------------
Dallas at 7/26/01 3:56:29 PM

Yes, sorry for being general and obtuse in that last send, but what I have is a developer who wrote a dts package that involves pulling data from an application SQL server to the Data warehouse. I haven't tried an output to text, but I can. Like I was saying the job runs fine, if you run it from where the DTS package was created (my machine last) but if I log on the server and try to run it, it says "the system cannot find the file specified."
I didn't even take the remote server piece into consideration. How do I schedule a job? Thanks, Dallas


------------
Diana at 7/26/01 3:45:07 PM

In other words, are you referencing files on a remote server?


------------
Diana at 7/26/01 3:44:01 PM

If you output your job outcome to a text file on failure, does it look like this?

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1
DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147024713 (800700B7)
Error string:
Error source:
Help file:
Help context: 0

Error Detail Records:
DTSRun OnFinish: DTSStep_DTSTransferObjectsTask_1
DTSRun: Package execution complete.




------------
Dallas at 7/26/01 3:24:10 PM

This is kind of a hybrid issue. When I or my developers create DTS packages we can usually run them fine from our machines, but when we go to schedule them, or run them from the Server Client we get an error that the package cannot be found. I realize that this must be some time of permissions or owner issue, but I don't know where to start looking. Should all of my DTS packages be owned by the SQL Agent Account? What am I overlooking?

[Anu]

Hi guys,

The property of the DTS package will take the functionality of the sqlserver where it runs. Normally when migrating from development to production, copying that as a structured storage file and save directly on the server (don't try remotely) and save that and then schedule there. Never even try from the client again.

If we follow this, we will get rid of all problems including the logins for the DTS package.
This will work perfectly.

Ref : Q269074 Microsoft Knowledge Base

All the best,
Anu







------------
Diana at 7/27/01 9:46:36 AM

Dallas -- I found the same problem (with the output that I duplicated in my first response). I could run it as a DTS package, but when I tried to schedule it, I would always get that output. I was using the "transfer objects" of DTS. What DTS would do is to create SQL scripts to run the package. When the scripts were placed on a local drive on the server, the job ran fine. What I suggest is to either map a permanent drive to the remote server -- you might also try using the Linked Servers option in EM.


------------
Dallas at 7/26/01 3:56:29 PM

Yes, sorry for being general and obtuse in that last send, but what I have is a developer who wrote a dts package that involves pulling data from an application SQL server to the Data warehouse. I haven't tried an output to text, but I can. Like I was saying the job runs fine, if you run it from where the DTS package was created (my machine last) but if I log on the server and try to run it, it says "the system cannot find the file specified."
I didn't even take the remote server piece into consideration. How do I schedule a job? Thanks, Dallas


------------
Diana at 7/26/01 3:45:07 PM

In other words, are you referencing files on a remote server?


------------
Diana at 7/26/01 3:44:01 PM

If you output your job outcome to a text file on failure, does it look like this?

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1
DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147024713 (800700B7)
Error string:
Error source:
Help file:
Help context: 0

Error Detail Records:
DTSRun OnFinish: DTSStep_DTSTransferObjectsTask_1
DTSRun: Package execution complete.




------------
Dallas at 7/26/01 3:24:10 PM

This is kind of a hybrid issue. When I or my developers create DTS packages we can usually run them fine from our machines, but when we go to schedule them, or run them from the Server Client we get an error that the package cannot be found. I realize that this must be some time of permissions or owner issue, but I don't know where to start looking. Should all of my DTS packages be owned by the SQL Agent Account? What am I overlooking?

[dallas]

Hi Guys,
Looks like I created my own problem without realizing it. I am running 2000 SQL tools on my machine and SQL 7.0 enterprise with only SP2 loaded on the server. I haven't tried this fix yet, but apparently if you reference Q274719 this is a bug that should be fixed with SQL 7.0 SP3. I'll give it a shot and see if it works and let you guys know. Thanks for the T&E, Dallas


------------
Anu at 7/27/01 1:09:51 PM

Hi guys,

The property of the DTS package will take the functionality of the sqlserver where it runs. Normally when migrating from development to production, copying that as a structured storage file and save directly on the server (don't try remotely) and save that and then schedule there. Never even try from the client again.

If we follow this, we will get rid of all problems including the logins for the DTS package.
This will work perfectly.

Ref : Q269074 Microsoft Knowledge Base

All the best,
Anu







------------
Diana at 7/27/01 9:46:36 AM

Dallas -- I found the same problem (with the output that I duplicated in my first response). I could run it as a DTS package, but when I tried to schedule it, I would always get that output. I was using the "transfer objects" of DTS. What DTS would do is to create SQL scripts to run the package. When the scripts were placed on a local drive on the server, the job ran fine. What I suggest is to either map a permanent drive to the remote server -- you might also try using the Linked Servers option in EM.


------------
Dallas at 7/26/01 3:56:29 PM

Yes, sorry for being general and obtuse in that last send, but what I have is a developer who wrote a dts package that involves pulling data from an application SQL server to the Data warehouse. I haven't tried an output to text, but I can. Like I was saying the job runs fine, if you run it from where the DTS package was created (my machine last) but if I log on the server and try to run it, it says "the system cannot find the file specified."
I didn't even take the remote server piece into consideration. How do I schedule a job? Thanks, Dallas


------------
Diana at 7/26/01 3:45:07 PM

In other words, are you referencing files on a remote server?


------------
Diana at 7/26/01 3:44:01 PM

If you output your job outcome to a text file on failure, does it look like this?

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1
DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147024713 (800700B7)
Error string:
Error source:
Help file:
Help context: 0

Error Detail Records:
DTSRun OnFinish: DTSStep_DTSTransferObjectsTask_1
DTSRun: Package execution complete.




------------
Dallas at 7/26/01 3:24:10 PM

This is kind of a hybrid issue. When I or my developers create DTS packages we can usually run them fine from our machines, but when we go to schedule them, or run them from the Server Client we get an error that the package cannot be found. I realize that this must be some time of permissions or owner issue, but I don't know where to start looking. Should all of my DTS packages be owned by the SQL Agent Account? What am I overlooking?

[jim]

DTS packages have different syntax depending on what SP you have applied, but they are backwards compatible. SP3 can run SP2 or SP1 packages, but not the other way around. SP3 is also compatible with SQL 2000. I haven't tried it yet, but I assume you will have the same problem with SP1 on SQL 2000.


------------
dallas at 7/31/01 5:00:04 PM

Hi Guys,
Looks like I created my own problem without realizing it. I am running 2000 SQL tools on my machine and SQL 7.0 enterprise with only SP2 loaded on the server. I haven't tried this fix yet, but apparently if you reference Q274719 this is a bug that should be fixed with SQL 7.0 SP3. I'll give it a shot and see if it works and let you guys know. Thanks for the T&E, Dallas


------------
Anu at 7/27/01 1:09:51 PM

Hi guys,

The property of the DTS package will take the functionality of the sqlserver where it runs. Normally when migrating from development to production, copying that as a structured storage file and save directly on the server (don't try remotely) and save that and then schedule there. Never even try from the client again.

If we follow this, we will get rid of all problems including the logins for the DTS package.
This will work perfectly.

Ref : Q269074 Microsoft Knowledge Base

All the best,
Anu







------------
Diana at 7/27/01 9:46:36 AM

Dallas -- I found the same problem (with the output that I duplicated in my first response). I could run it as a DTS package, but when I tried to schedule it, I would always get that output. I was using the "transfer objects" of DTS. What DTS would do is to create SQL scripts to run the package. When the scripts were placed on a local drive on the server, the job ran fine. What I suggest is to either map a permanent drive to the remote server -- you might also try using the Linked Servers option in EM.


------------
Dallas at 7/26/01 3:56:29 PM

Yes, sorry for being general and obtuse in that last send, but what I have is a developer who wrote a dts package that involves pulling data from an application SQL server to the Data warehouse. I haven't tried an output to text, but I can. Like I was saying the job runs fine, if you run it from where the DTS package was created (my machine last) but if I log on the server and try to run it, it says "the system cannot find the file specified."
I didn't even take the remote server piece into consideration. How do I schedule a job? Thanks, Dallas


------------
Diana at 7/26/01 3:45:07 PM

In other words, are you referencing files on a remote server?


------------
Diana at 7/26/01 3:44:01 PM

If you output your job outcome to a text file on failure, does it look like this?

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSTransferObjectsTask_1
DTSRun OnError: DTSStep_DTSTransferObjectsTask_1, Error = -2147024713 (800700B7)
Error string:
Error source:
Help file:
Help context: 0

Error Detail Records:
DTSRun OnFinish: DTSStep_DTSTransferObjectsTask_1
DTSRun: Package execution complete.




------------
Dallas at 7/26/01 3:24:10 PM

This is kind of a hybrid issue. When I or my developers create DTS packages we can usually run them fine from our machines, but when we go to schedule them, or run them from the Server Client we get an error that the package cannot be found. I realize that this must be some time of permissions or owner issue, but I don't know where to start looking. Should all of my DTS packages be owned by the SQL Agent Account? What am I overlooking?