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.

Call or Reference a Stored Procedure from a published report

Hi All,
Got a report published that uses a stored procedure which is on a different
server in our Domain. I have the correct connection string info entered via
the Report Manager / Data Sources tab, but how and where does one reference
the SP to execute it? The SP does work in BIDS.
Thanx,
~SDDid you check if the user that reads the report has the execute permission
on the SP?
What error message are you getting?
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"iamscott" <iamscott@.discussions.microsoft.com> wrote in message
news:D3B5C2A0-49AC-4786-AE1C-68990FF487D1@.microsoft.com...
> Hi All,
> Got a report published that uses a stored procedure which is on a
> different
> server in our Domain. I have the correct connection string info entered
> via
> the Report Manager / Data Sources tab, but how and where does one
> reference
> the SP to execute it? The SP does work in BIDS.
> Thanx,
> ~SD
>|||The reference to the SP is in the report. If the report uses a shared
datasource then the datasource is used to get the connection and the report
RDL specifies the stored procedure. The rendering engine then executes the
SP. There is no way to force the SP to execute other than trying to run the
report.
But, it is also possible to embed the datasource into the report rather than
used a shared datasource. If that is the case and it is wrong or has some
issue with it then you will have problems. Most people use shared data
sources.
You say it has the correct connection information. What type of credentials
are you using. Are you securely storing the credentials or are you using
integrated security. If integrated then it will be using the windows
credentials of the user running the report which might not have correct
permissions for SQL Server. I always securely stored the credentials. I use
a SQL account that I have as a read only account which is given execute
permission on any necessary stored procedures. You can also do this with a
domain account but I prefer running in mixed mode and uses a SQL account.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"iamscott" <iamscott@.discussions.microsoft.com> wrote in message
news:D3B5C2A0-49AC-4786-AE1C-68990FF487D1@.microsoft.com...
> Hi All,
> Got a report published that uses a stored procedure which is on a
> different
> server in our Domain. I have the correct connection string info entered
> via
> the Report Manager / Data Sources tab, but how and where does one
> reference
> the SP to execute it? The SP does work in BIDS.
> Thanx,
> ~SD
>