Export Data using DTS 1

[Indika]

I have two NT servers in a small domain with one running IIS and the other SQL 2000. I logged into both server using a domain user account. Account is part of the local administrator group in both servers. I want to create a DTS job and schedule it to run at night. I am trying to export some data and put it into a text file. I want to save the file on a share on IIS server.

Problem I am having, I can create a DTS job and schedule it on the database server and it runs fine (job creates the text file), but if I try to save the job on the IIS server it fails.

Does anyone have any suggestions? Thanks.

 

[JayKusch]

Why are you trying to save it on the server w/ IIS??? Just create a share on the target server and have the DTS package create the needed file there.

Thanks

J. Kusch

 

[Indika]

That was my mistake. I am not trying to save the job on the IIS server. Job is on the database server. I have a share on the target server. I want the job to create a text file on the IIS server share. But it fails.

If I manually execute the DTS Package, then it creates the text file on the target share. If I try to manually run the job or try to schedule the DTS package, then it fails.

 

[JayKusch]

Make sure the DTS Job that is created in the Jobs folder in Enterprise Manager has the correct rights set on it. What I mean by this is that the "owner" of the job needs to have rights to create the file on the target server.

Thanks

J. Kusch

 

[Indika]

Thanks J. That fixed the problem

Scheduled DTS jobs use SQL Agent service startup account. I had target server share permission set only for the DTS package owner. Looks like both accounts need write permission.