MS SQL Server DTS - DTS Error

Author

DTS Error

Travis

2005-09-21, 3:24 am

Hi ,

I created a DTS package and it running fine when I run in design view.
But when I scheduled it , it fail to run and I get this kind of error ,

"Step 'DTSStep_DTSExecuteS
QLTask_1' succeeded
Step Execution Started: 9/21/2005 10:45:53 AM
Step Execution Completed: 9/21/2005 10:45:53 AM
Total Step Execution Time: 0.063 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPump
Task_1' failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset

Provider

Step Error Description:Error opening datafile: Access is denied. "

I suspect is the security issue but I am not sure where can I debug it.
- The package was created under user "sa" - SQL Server Admin
- The package was scheduled under user "sa" - SQL Server Admin

- The DTS package will extract the data from a csv file from a "Share
Folder"
- The "Share Folder" was restricted to only certain users

Please advise.

One more issue , how to I show the "Step Name" in my error log instead of
showing the " DTSStep_DTSDataPumpT
ask_1" ?

Travis Tan

Allan Mitchell

2005-09-21, 3:24 am

Usually when something succeeds in Enterprise Manager and fails when
executed as a job you can narrow it down to here

http://support.microsoft.com/?kbid=269074

About renaming steps? Do it at the very start of the package and you
can do it in disconnected edit.

Allan

"Travis" <Travis@discussions.microsoft.com> wrote in message
news:4033CE26-9EBF-4D9A-BC87- 490EC56D9691@microso
ft.com:

> Hi ,
>
> I created a DTS package and it running fine when I run in design view.
> But when I scheduled it , it fail to run and I get this kind of error ,
>
> "Step 'DTSStep_DTSExecuteS
QLTask_1' succeeded
> Step Execution Started: 9/21/2005 10:45:53 AM
> Step Execution Completed: 9/21/2005 10:45:53 AM
> Total Step Execution Time: 0.063 seconds
> Progress count in Step: 0
>
> Step 'DTSStep_DTSDataPump
Task_1' failed
>
> Step Error Source: Microsoft Data Transformation Services Flat File
> Rowset
>
> Provider
>
> Step Error Description:Error opening datafile: Access is denied. "
>
> I suspect is the security issue but I am not sure where can I debug it.
> - The package was created under user "sa" - SQL Server Admin
> - The package was scheduled under user "sa" - SQL Server Admin
>
> - The DTS package will extract the data from a csv file from a "Share
> Folder"
> - The "Share Folder" was restricted to only certain users
>
> Please advise.
>
> One more issue , how to I show the "Step Name" in my error log instead
> of
> showing the " DTSStep_DTSDataPumpT
ask_1" ?
>
> Travis Tan

Travis

2005-09-21, 7:23 am

Hi Allan ,

I am not sure what is wrong here. I create a package under 'sa'
and I scheduled under 'sa' but when I check the windows event log
the scheduled is run on domain user ...

Where can I change the setting ? Please advice

--
Travis Tan

"Travis" wrote:

> Hi ,
>
> I created a DTS package and it running fine when I run in design view.
> But when I scheduled it , it fail to run and I get this kind of error ,
>
> "Step 'DTSStep_DTSExecuteS
QLTask_1' succeeded
> Step Execution Started: 9/21/2005 10:45:53 AM
> Step Execution Completed: 9/21/2005 10:45:53 AM
> Total Step Execution Time: 0.063 seconds
> Progress count in Step: 0
>
> Step 'DTSStep_DTSDataPump
Task_1' failed
>
> Step Error Source: Microsoft Data Transformation Services Flat File Rowset
>
> Provider
>
> Step Error Description:Error opening datafile: Access is denied. "
>
> I suspect is the security issue but I am not sure where can I debug it.
> - The package was created under user "sa" - SQL Server Admin
> - The package was scheduled under user "sa" - SQL Server Admin
>
> - The DTS package will extract the data from a csv file from a "Share
> Folder"
> - The "Share Folder" was restricted to only certain users
>
> Please advise.
>
> One more issue , how to I show the "Step Name" in my error log instead of
> showing the " DTSStep_DTSDataPumpT
ask_1" ?
>
> Travis Tan

Allan Mitchell

2005-09-21, 8:24 pm

So the creator of the package has no relevance here. What does is the
account under which the job runs.

Any member of the sysadmin role and the job executes as the SQL Server
Agent Service Account
If the "runner" is not a member of the sysadmin role then the job is
executed as the proxy account.

Allan

"Travis" <Travis@discussions.microsoft.com> wrote in message
news:3049D439-D961-4450-82C2- 9E953E82CA9A@microso
ft.com:
[color=darkred]
> Hi Allan ,
>
> I am not sure what is wrong here. I create a package under 'sa'
> and I scheduled under 'sa' but when I check the windows event log
> the scheduled is run on domain user ...
>
> Where can I change the setting ? Please advice
>
> --
> Travis Tan
>
>
> "Travis" wrote:
>
>
> view.
> ,
> Rowset
> it.
> "Share
> instead of

Travis

2005-09-22, 3:23 am

Hi Allan ,

I right click on the SQL Server Agent properties , the "Services" was
startup using
Windows Account and the "Connection" also under Windows Account.

When I execute the Jobs , the error msg was "Access Denied" ... my DTS
was read from the csv file in "Share Folder" on server local hard disk.

The job owner I set using "sa" which is SQL Admin .... anything wrong
with that ?

How do I set the runner as 'sa' ?

--
Travis Tan

"Allan Mitchell" wrote:

> So the creator of the package has no relevance here. What does is the
> account under which the job runs.
>
> Any member of the sysadmin role and the job executes as the SQL Server
> Agent Service Account
> If the "runner" is not a member of the sysadmin role then the job is
> executed as the proxy account.
>
> Allan
>
> "Travis" <Travis@discussions.microsoft.com> wrote in message
> news:3049D439-D961-4450-82C2- 9E953E82CA9A@microso
ft.com:
>
>
>

Allan Mitchell

2005-09-22, 3:23 am

The job is owned by sa, therefore the "runner" is the Agent account.
Permissions on to the share have nothing to do with SQL permissions and
a lot to do with NT permissions.

Share mappings are user specific so use UNC.

Does the SQL Server Agent service account user have permissions onto the
directory in which the file is located?

Allan

"Travis" <Travis@discussions.microsoft.com> wrote in message
news:7806AFB9-A005-41F4-91C0- A3C6CD730368@microso
ft.com:
[color=darkred]
> Hi Allan ,
>
> I right click on the SQL Server Agent properties , the "Services" was
> startup using
> Windows Account and the "Connection" also under Windows Account.
>
> When I execute the Jobs , the error msg was "Access Denied" ... my DTS
> was read from the csv file in "Share Folder" on server local hard disk.
>
> The job owner I set using "sa" which is SQL Admin .... anything wrong
> with that ?
>
> How do I set the runner as 'sa' ?
>
>
> --
> Travis Tan
>
>
> "Allan Mitchell" wrote:
>
>
>
>
> design
>
>
> error
>
>
> File
>
>
> denied. "
> debug
>
>
>
>
>
>
>

Sponsored Links