Calling DTS via ASP.NET works locally but not when published to intranet

I have a DTS package that I am calling in my code-behind (vb) of one of my asp.net pages on my intranet. The package takes a FoxPro table that is on our network and inserts it into a SQL Server table in one of our databases. The package gets called and executed properly when I am working on it in development on my local workstation, but when I publish it to our intranet, which is in the same domain and behind the same firewall, the package does not execute. No error shows up on the published page, but also nothing gets executed. Has anyone encountered this? Here is my code, which is pretty straightforward:

oPkg = Server.CreateObject(

"DTS.Package")
oPkg.LoadFromSQLServer("[servername]","sa","[password]", DTSSQLStgFlag_Default,"","","","Invoice Import")
oPkg.Execute()

Any help would be appreciated. Thanks.

The problem is you are using the wrong user to run the DTS package because when Asp.net is running the package it runs under the Agent so the better security model is to clone a valid Admin level Windows and SQL Server account to run the package. I have covered it in the thread below, if you run into problem try again it will work. Hope this helps.

http://forums.asp.net/thread/1522519.aspx

|||

Isn't asp.net running the package when I am going through my browser on localhost? Could you elaborate on how asp.net would use sa effectively when I'm using a browser in VS and it wouldn't when I publish? That doesn't make much sense.

|||

I have explained in detail in the thread I posted earlier it is not SA(system Admin) related you need an account with Network permissions for the SQL Server Agent service which is reponsible for automations in SQL Server. You could be using browser in VS but this is something only SQL Server control and I have wondered why it is barely documented but running DTS in web applications was created by SQL Server users not Microsoft so documentation is not comprehensive. What I know is if you don't give the Agent the right permissions it will not work.

So you need a domain account with Admin level permissions create Admin level SQL Server permissions then use that account to create a proxy account for SQL Server Agent to run your package with xp_cmdshell. You are not the first to ask this question and will not be the last but read that thread do everything correctly and it will work. Hope this helps.