run a DTS

Hi,

I am trying to run a DTS that export the table to a different server as an excel file.

In the DTS I specified UNC path for the file to be exported.

Now, I login the SQL Server with a network user MyUser and run the package manually and I see the excel file is created in the remote server. I want to do the same thing with a SQL Agent Job task. I created a stored procedure and put my DTSRun all in it as follows;

dtsrun /S MyServer /U MyUSer /P MyPass /N MyDTS . I also create the job and make the owner and “RunAs” MyUser and run the task I get the following error. Apparently since MyUser is a network user, it could not login to SQL Server as I defined in my DTSRun. Is there any way I can get this DTS run under that user without changing SQL Agent user which is running under a system user.

Error:

DTSRun:Loading...

Error:-2147217843 (80040E4D); Provider Error:18456 (4818)

Error string:Login failed for user ‘MyUSer’.

Error source:Microsoft OLE DB Provider for SQL Server

Help file:

Help context:0

(null)

First make a login for your MyUser windows account and assign it to the sysadmin role - make sure that MyUser is a local admin on the server that SQL is running on. Then create a Proxy account for the SQL Server (right click on the agent and select properties, then go the the Job System) use MyUser as the proxy account. Look at SQL Server Agent Properties (Job System Tab) on BOL for more information.

Brad