Hi,
I have a DTS package that imports data into SQL 2000 from an ODBC source on
another server.
There are only 2 steps, empty the sql table and import new data.
This package runs with no errors when it is run from EM on the SQL server.

I need to be able to trigger this package from a ASP page on an intranet
server, but I cannot get it run correctly.
Both steps return failed message to the asp page. I suspect it could be a
permissions problem.
What permissions do I need to set where?

Thanks,
Rogerc

What's the error message you get?

Axel Dahmen

------------
"RogerC" <rojoch (AT) NOSPAMtiscali (DOT) co.uk> schrieb im Newsbeitrag
news:OEvczAmMFHA.3708 (AT) TK2MSFTNGP14 (DOT) phx.gbl...

Hi Axel,
Thanks for responding - sorry about delay getting back.

The result of ruuning the asp page is:

Step [DTSStep_DTSExecuteSQLTask_1] failed
Task "Execute SQL Task: Delete Table Data"
Step [DTSStep_DTSDataPumpTask_1] failed
Task "Transform Data Task: Import Data"
Package [RPackage2] failed

So the package is loading ok, but cannot execute the steps.
What I cannot work out is what permissions are required where and what runs
under what account.
The job involves 3 servers - IIS, sql 2000 and an access database source on
another.

When a workstaion calls the asp page on the IIS server does the package run
on the IIS server? and with what account (IUSR_<WEBSERVER>) ?
Im using sql authentication in the package to access the sql table.

RogerC

"Axel Dahmen" <NO_SPAM (AT) NoOneKnows (DOT) invalid> wrote

Hi Roger,

well, I thought you could get more precise on the errors appearing.

But you are right, this may be a rights problem. Which authentication scheme
are you using? IIS will execute the ASP page using IUSR_<webserver> unless
you are running the application as a separate process.

What code are you using to execute the DTS package?

HTH,
Axel Dahmen

------------
"RogerC" <rojoch (AT) NOSPAMtiscali (DOT) co.uk> schrieb im Newsbeitrag
news:OAzfG9jNFHA.4092 (AT) tk2msftngp13 (DOT) phx.gbl...

Hi Axel,
The code I am using is directly from
http://support.microsoft.com/?kbid=252987 with SQL authentication.
This section from the paragraph titled 'Possible Permissions Issues'
"make sure that the account executing the package has appropriate Windows
permissions to all resources that the package may use--such as disk files,
DLLs, or other drivers (for import/export operations)--especially when the
Web site is configured for Anonymous access" is pretty vague!

I have since had some success by doing the following:
Installing IIS on the SQL server and getting the page to run from there. I
had to give the Access database 'Everyone' read permissions to achieve this.
I assume that this was because the IUSR__<webserver> account is a local
account.
Ensuring that when setting up the DTS package in Enterprise manager the SQL
server name is entered correctly - if working directly on the SQL server it
will default to 'local' and have to be entered manually as it only appears
as 'local' in the drop down list. When this package is run on the IIS server
it doesn't know what 'local' is.

I now get the following on the SQL server, the IIS Server and from a
workstation-

Step [DTSStep_DTSExecuteSQLTask_1] succeeded
Task "Execute SQL Task: Delete Table Data"
Step [DTSStep_DTSDataPumpTask_1] succeeded
Task "Transform Data Task: Import Data"
Package [RPackage2] succeeded

The section 'Using DTS Events' mentions creating a compiled ASP component
to get access to the GetExecutionErrorInfo method.
I will have to look into this as the errors returned from a script are not
helpful

Regards,
RogerC


"Axel Dahmen" <NO_SPAM (AT) NoOneKnows (DOT) invalid> wrote

This might be your problem. You should have a look at "Connecting to
Multiple Instances of SQL Server" in Online Books.

Which server name are you using for oPkg.LoadFromSQLServer "MyServer" ? This
should be the name to be used in the DTS connection string instead of
<local>

Did this help?

Axel Dahmen
www.sportbootcharter.com