DTS Package Fails

[JohnBates]

Hi SQL Server pros -

I'm using SQL Server 7 in an NT 4 environment.

I built a DTS package to copy data from a file on the AS400
to my SQL Server 7 table.

The package runs perfectly when I run it 'on-demand' ... (when I right-click on the package name, click Execute)

But if I schedule the package, it always fails when it tries to execute. I specified an error log file for the package but the information that gets logged is not detailed enough to be of any help in isolating the problem.


Again, package works when I run it manually, but always fails when it runs as a scheduled job.

What might the problem be ?

Thanks, John

 

[JohnBates]

Here is some more information:
It successfully clears the target table (removes all rows) but fails on the copy data step.

This was in the Job History...

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Delete from Table [frigdare].[dbo].[Customers] Step DTSRun OnFinish: Delete from Table [frigdare].[dbo].[Customers] Step DTSRun OnStart: Copy Data from Results to [frigdare].[dbo].[Customers] Step DTSRun OnError: Copy Data from Results to [frigdare].[dbo].[Customers] Step, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Micros. The step failed.

 

[tlbroadbent]

Jobs run under different security when started by the agent than when started in Enterprise Manager manually. Check the article about "How to Run a DTS Package as a Scheduled Job" at

http://support.microsoft.com/support/kb/articles/Q269/0/74.ASP.

See also faq183-280. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JohnBates]

Thanks Terry for your reply. I read the FAQ and the Microsoft article.

Ive tried several things since my original post....
now I get a "Data source name not found" error in the
Job history..... here is the complete detail:

... DTSRun: Executing... DTSRun OnStart: Copy Data from Results to [frigdare].[dbo].[Customers] Step DTSRun OnError: Copy Data from Results to [frigdare].[dbo].[Customers] Step, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: Copy Data from Results to [frigdare].[db... Process Exit Code 1. The step failed.

Since the job runs OK when I run it "manually", but fails as a scheduled job, I copied the .dsn file onto the SQL Server machine itself. Still fails with same error.

I also changed the owner of the job to 'sa'. JOb still fails for same reason.

It doesn't seem like it is related to authority/permissions
but I could be wrong.

I'm puzzled. I welcome anyone's ideas.
Thanks, John

 

[tlbroadbent]

Try setting up the DSN as a System DSN on the server rather than a file DSN. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[JohnBates]

Thanks tlbroadbent ,

I did as you suggested, set up a DSN at the SQL Server. First though I had to install IBM's Client Access Express onto the Server so I could use the ODBC driver for the AS400 in my DSN.

Now the package runs when scheduled as a Job.

Thanks, John